A few words of thanks would be greatly appreciated.

Excel - Copy a range if the a specific column is blank


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 


Assuming that column H does not have any blank cells so it will be used to stop the loop:

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

Note that

Thanks to Helper for this tip on the forum.


A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Copy a range if the a specific column is blank », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).