Excel VBA, How to select rows based on data in a column?

Question

Sub SelectAllReleventText()
Do While Range(“A1”).Offset(1, 6) <> Empty
Rows(ActiveCell.Row).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Here is my script, I've been told it doesn't do what it is meant to, which I expected since this was my first attempt. I am coming up with a variable not defined error. I thought I defined the variable, but I guess it wasn't specific enough for Excel VBA.

This is what I am attempting to do.

In Workbook 1, On B6 there is an alphanumeric name, I want that row to be selected.
Go down one row, if there is text there select that row.
Continue till text is no longer prevalent.
Copy selected rows.
Paste into another workbook (Workbook2), into tab 1, starting on row 2, since row 1 has headers.

Thanks in advance. Just a heads up, I am using the Options Explicit in my VBA because I was told it was the "right way to do thing"...

try this: Do While Range(“A1”).Offset(1, 6) <> ""

assylias
– assylias

2012-03-20 16:36:18 +00:00
Commented Mar 20, 2012 at 16:36 — assylias
– assylias, Commented Mar 20, 2012 at 16:36

Siddharth Rout · Accepted Answer · 2012-03-20 16:42:16Z

11

Yes using Option Explicit is a good habit. Using .Select however is not :) it reduces the speed of the code. Also fully justify sheet names else the code will always run for the Activesheet which might not be what you actually wanted.

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim lastRow As Long, i As Long
    Dim CopyRange As Range

    '~~> Change Sheet1 to relevant sheet name
    With Sheets("Sheet1")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 2 To lastRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then
                If CopyRange Is Nothing Then
                    Set CopyRange = .Rows(i)
                Else
                    Set CopyRange = Union(CopyRange, .Rows(i))
                End If
            Else
                Exit For
            End If
        Next

        If Not CopyRange Is Nothing Then
            '~~> Change Sheet2 to relevant sheet name
            CopyRange.Copy Sheets("Sheet2").Rows(1)
        End If
    End With
End Sub

NOTE

If if you have data from Row 2 till Row 10 and row 11 is blank and then you have data again from Row 12 then the above code will only copy data from Row 2 till Row 10

If you want to copy all rows which have data then use this code.

Option Explicit

Sub Sample()
    Dim lastRow As Long, i As Long
    Dim CopyRange As Range

    '~~> Change Sheet1 to relevant sheet name
    With Sheets("Sheet1")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 2 To lastRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then
                If CopyRange Is Nothing Then
                    Set CopyRange = .Rows(i)
                Else
                    Set CopyRange = Union(CopyRange, .Rows(i))
                End If
            End If
        Next

        If Not CopyRange Is Nothing Then
            '~~> Change Sheet2 to relevant sheet name
            CopyRange.Copy Sheets("Sheet2").Rows(1)
        End If
    End With
End Sub

Hope this is what you wanted?

Sid

answered Mar 20, 2012 at 16:42

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Ridge Over a year ago

The second one is very close to what I am looking for. What I want to do is copy the data from Backorder.cvs to JobsDB.xlsx, under the 'New' tab. Here is the attachment of both. dl.dropbox.com/u/3327208/Jobs.zip

Siddharth Rout Over a year ago

Gr8 :) What do you mean by "close"? Which part is not working (if it is not working) Did you make relevant changes to the above code to suit your needs? If yes, then I would love to see the code that you tried :)

Matt Ridge Over a year ago

I just posted the attachment as a link... it will explain what I meant originally, and I'll be back soon, I got an emergency trip I got to take.

Matt Ridge Over a year ago

Sorry about leaving it like that. I had a client that had her laptop dropped by her son, had to recover all the media and data on the drive. It wasn't as SSD. In any case, instead of saying what I want, I've uploaded the final attachment with all the documents, and explanation of what I need to do. If you can help it would be greatly appreciated. dl.dropbox.com/u/3327208/zips/Late%20Jobs.zip

Tmdean · Accepted Answer · 2012-03-20 17:15:39Z

The easiest way to do it is to use the End method, which is gives you the cell that you reach by pressing the end key and then a direction when you're on a cell (in this case B6). This won't give you what you expect if B6 or B7 is empty, though.

Dim start_cell As Range
Set start_cell = Range("[Workbook1.xlsx]Sheet1!B6")
Range(start_cell, start_cell.End(xlDown)).Copy Range("[Workbook2.xlsx]Sheet1!A2")

If you can't use End, then you would have to use a loop.

Dim start_cell As Range, end_cell As Range

Set start_cell = Range("[Workbook1.xlsx]Sheet1!B6")
Set end_cell = start_cell

Do Until IsEmpty(end_cell.Offset(1, 0))
    Set end_cell = end_cell.Offset(1, 0)
Loop

Range(start_cell, end_cell).Copy Range("[Workbook2.xlsx]Sheet1!A2")

Collectives™ on Stack Overflow

Excel VBA, How to select rows based on data in a column?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related