Copy macro with blank cells


Posted by Scott Westfield on January 10, 2002 2:20 PM

I am using the following macro (which was modified from a former tip of the week) to copy rows of data starting at column H from Sheet1 to Sheet2 based on the value of column n.

This macro works as there is data in each row starting at column H. I now need to copy column G as well. Column G often has blank cells. When I modify the macro to include column G it stops copying when it finds a blank cell. I need it to continue copying until row H is blank.

Public Sub CopyRows()
Sheets("Sheet1").Select
' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
' Loop through each row
For x = 17 To FinalRow
' Decide if to copy based on column n
ThisValue = Range("n" & x).Value
If ThisValue = "Y" Then
Range("h" & x & ":i" & x).Copy
Sheets("Sheet2").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End If
Next x



Posted by Joe Was on January 10, 2002 3:20 PM

Add the Offset(row,col)
-1,0 is up one row and (1,0) is down one row!
FinalRow = Range("A65536").End(xlUp).Row.Offset(1,0)

Will give you the row below the first blank cell in column A. You may need to increase the offset. JSW