Copy Data Less Headings

  • hi guys,
    have a tricky one... how do i go about coding a macro so that it can look at a column,lets say column F in this case, on a worksheet and figure out how many rows contain data, select all rows BUT the header row (row 1 in this case), select them for copy, and paste them to another spreadsheet in the xls file starting from row 2, pasting values only.

    the way i would LIKE to do this function is to take column 1, determine its length, or how many rows contain data and where the first empty row kicks in, anchor that into a variable and use that variable to set the copy paste function for the remaining columns i run the function on.

    why do i need this done? i'm makign a macro that can automatically build reports for me from raw data dumpted from the system.... and later columns in the spreadsheet have empty cells, so i want the function to process at a fixed length with all cells using column one as a guide for the copy/paste function.

    make sense? i'd appreciate if somebody could give me a idea of what the code would look like.


  • Re: Column Selection

    Something like this will get you started, I do a lot of list and data parsing also, your best friend is "worksheetfunction.counta" :)

    Alternately, you could use a resize

    note I didnt test this, there might be a syntactical error(s) but the code works I've done these same things many times. You might have to mess around with the x-1 part of the resize, I think itll do what youll looking for but its easy to change. Just throw the selection.resize statement in its own sub, put real solid numbers in instead of variables, and play with how it changes the selected area.

    edit: just saw that you might have empty rows in columns after the first one. Thats easy to fix:

  • Re: Column Selection

  • Welcome to the forum.

    Your thread title won't help anyone looking for help with a similar problem. Google your thread title and see how helpful it is: Column Selection

    Please read the Rules you agreed to when joining. In particular the point on Thread Titles. After you have done so please CLICK HERE and Private Message me or another mod royUK;Jack in the UK;Richie(UK);Dave Hawley;ByTheCringe2 with your acceptance, or non-acceptance of the rules and a suggested change for your thread title. Please also include a link to this Thread. You can copy this directly from the Address bar of your Browser. When this is done your thread will be re-opened.

    [COLOR="Red"]REMEMBER:[/COLOR] Your question Title must accurately and concisely describe your problem, NOT what you THINK is your answer and use only relevant keywords.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!