ExcelBanter - View Single Post - How do I avoid copying data into blank cells

Dove

wrote:
I have data in cells in rows C,D,AF&BL Im copy and pasting to another
worksheet. What im trying to do is if there is not data in cells in row
AF&BL I dont want it to copy the column onto the new worksheet. Cells
in row C&D will always have data in it.

I.E 1) column no 3 has data in row C&D but NO data in AF & BL . I do
not want any data in that column(inc columns c&d) copied over.
2) column no4 has data in row C&D and also data in either AF&BL this
one I DO want copied over.

When I mean copied over I mean the data in cells C,D,AF&BL.

If I have what you are looking for straight (I was left a little
confused as to what you are calling rows and columns in the second
paragraph), something like the following is what you are looking for:

==================================

' Substitue the column letters in [ ] for the numeric equivalent (i.e.
C = 3)

Dim i as Integer
Dim c as Integer
Dim wsData as Worksheet
Dim wsPaste as Worksheet

Set wsData = Worksheets("[name of sheet copying from]")
Set wsPaste = Worksheets("[name of sheet pasting to]")

c = [the starting row number on the sheet you are pasting to, just use
"i" if you don't care about empty rows]

wsData.Activate

For i = 1 to ActiveSheet.UsedRange.Rows.Count ' Change 1 to 2 if you
have a header row

If (Cells(i,[AF].Value < "") Or (Cells(i,[BL].Value < "") Then
Rows(i).Select
Selection.Copy
wsPaste.Activate 'I am doing the paste part from memory, may
be off...
Rows(c).Select
ActiveSheet.Paste
c = c + 1
wsData.Activate
End If
Next i

Set wsData = Nothing
Set wsPaste = Nothing

==============================

You may want to set the screen updating to false before going into the
for loop and back to true after to loop unless you want Excel to blink
around as it steps through the loop, or you may want to use the status
bar to count the row you are working on (displaying the value of i) to
keep track of where it is...

David