Copy a range if the a specific column is blan

Q - Jan 22, 2009 at 10:22 AM
 Helper - Feb 2, 2009 at 09:03 PM

I'm trying to copy a row of data from column A - G if column D from row 8 - 209 is blank to the bottom of my spreadsheet.

I used the following coding, but it's copying the whole row instead of up to column G.

Sub Unposted()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 8
LSearchRow = 8

'Start copying data to row 170 in WSO - 480251444 (Op) (row counter variable)
LCopyToRow = 223

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column D = "", copy entire row to WSO - 480251444 (Op)
If Range("D" & CStr(LSearchRow)).Value = "" Then

'Select row in WSO - 480251444 (Op) to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select

'Paste row into WSO - 480251444 (Op) in next row
Sheets("WSO - 480251444 (Op)").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("WSO - 480251444 (Op)").Select

End If

LSearchRow = LSearchRow + 1


'Position on cell A4
Application.CutCopyMode = False

MsgBox "All matching data has been copied."

Exit Sub

MsgBox "An error occurred."

End Sub

Please help!!

3 replies

1. Column H does not have any blank cells so it will be used to stop the loop.

I tried this with test data based on your description.

Private Sub CommandButton1_Click()

Dim r
r = Range("A65536").End(xlUp).Row
Dim i
Dim j
j = 8

Do While Not IsEmpty(Range("H" & j))

If IsNumeric(Range("A" & j)) And Range("D" & j) = "" Then
r = r + 1
Range("A" & r) = Range("A" & j)
Range("B" & r) = Range("B" & j)
Range("C" & r) = Range("C" & j)
Range("D" & r) = Range("D" & j)
Range("E" & r) = Range("E" & j)
Range("F" & r) = Range("F" & j)
Range("G" & r) = Range("G" & j)
End If

j = j + 1

End Sub
Tweedledum Posts 181 Registration date Monday January 19, 2009 Status Member Last seen April 2, 2009 125
Jan 22, 2009 at 11:23 AM
I dont actually get what you want, do you want to search til you find a blank row? if so its would be something like this

With ActiveSheet
LRow = .Cells(.Rows.Count, "G").End(xlUp).Row

that will count through the rows, sorry i dont have much time and i amn't a vba master but the XLup idea might get you on the right road, if you google it you will find others for going left right up down etc if i have time i might read through your post a but slower and try to see if there is an easy solution

Thanks for the help, but I'm really new at this so I'm not even sure where that code u replied with goes.

Just to clarify, I have a spreadsheet that I update daily that has data starting at A8 through column M. I want to look in column D for blank spaces if column A has an integer in it. If it does, I want to copy that row of data from column A to G to the bottom of the spreadsheet.

This coding does that, but instead of copy just columns A to G it's copying the entire row. This is causing the data in column H to M to disappear.

Does that help?

---again, thanks for the help!
Tweedledum Posts 181 Registration date Monday January 19, 2009 Status Member Last seen April 2, 2009 125
Jan 23, 2009 at 04:56 AM
to be honest i know very little about vba i have only done one or two projects in it, i would suggest signing up to technet forums because they have a vba forum with people who are actually experts. But i think you might have to define a range to get it to just take a few columns. It would be something like

Dim rDest As Range ' creates range variable
Set rDest = Range("G" & cnt) 'sets range variable

When i set rDest to the range cnt is a counter which updates in a loop so it isnt helpful but if you look for defining ranges on google you might find a way to set the range between two column values. Sorry i cant be of more help but its a avenue of research that might help you.