Results 1 to 6 of 6

Thread: Need vba code to select only cells with data

  1. #1

    Need vba code to select only cells with data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have created a macro to copy subsets of data to a new workbook by filtering the main set of data. It would appreciated if someone would provide me with code to select only the cells with data(in the new workbook) so I can copy that data to another sheet. Currently the copy procedure copies all the rows in the sheet, including the blank rows which recreates a huge file. Thank you in advance for any assistance you may provide.

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    183
    Articles
    0
    Excel Version
    2010
    Are the blank rows within the data or beneath it?
    Circumference of a circle = 2πr²



    ²the circle's radius

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    you could have the macro go line by line and copy the data only if cell A? has data in it.
    This kind of code will take a long time to execute.

    Or you could sort the master file to get all the blank rows at the bottom of the range.
    then recalculate how many rows are being used.
    then copy your entire range at once.

    Simi

  4. #4
    sorry for the delayed response. I appreciate your posts. the data is contiguous from A1 to last col and last row.

  5. #5
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    I have needed this same kind of thing several times.


    '
    Code:
    'initalize variables 
       Dim lRows As Long 
       Dim lCols as Long 
               
    'calculate last row and last column
       With ActiveSheet 
                 lRows = .Cells(.Rows.Count, "A").End(xlUp).Row 
                 lCols = .Cells(1, .Columns.Count).End(xlToLeft).Column 
               
                 'copy data 
                 .Range(cells(1,1), cells(lRows, lCols)).Copy 
         End With

    Hope this helps

    sorry the formatting sucks.
    Last edited by Simi; 2013-04-25 at 11:39 PM. Reason: formatting code problems

  6. #6
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Try

    Code:
    Sheet1.Cells(1, 1).CurrentRegion.Offset(1).Copy Sheet2.Cells(1, 1)
    Offset prevents a header row being copied
    Hope that helps

    Roy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •