Copy a range if the a specific column is blan Closed

Question

Hello,

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
            Selection.Copy

            'Paste row into WSO - 480251444 (Op) in next row
            Sheets("WSO - 480251444 (Op)").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste

            '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

    Wend

    'Position on cell A4
    Application.CutCopyMode = False
    Range("A4").Select

    MsgBox "All matching data has been copied."

    Exit Sub

Err_Execute:
    MsgBox "An error occurred."

End Sub



Please help!!

Helper · Accepted Answer

Assumptions:
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
Loop

End Sub

Tweedledum · Answer

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

Tweedledum · Answer

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.

Copy a range if the a specific column is blan

3 responses

Similar discussions

Subscribe To Our Newsletter!