Selecting the rows of each cell within a range object (VBA)

Question

I am trying to copy a range of data from one worksheet to another. The range is defined by the value in the A column matching a set value. I have been able to add each of the cells with the specified value into a range object, but I now have to select all the data in the rows of the cells which are in my range object in order to copy them to another sheet. Any advice?

Also, I am very new to VBA so I am sure my code formatting is terrible, but I really just need a solution to this particular problem. Thanks for the help!

Dim allAsNum As Range
Dim currAsNum As Range
Dim asnum
Dim j

Sheets("Full Log").Select
asnum = "searchingvalue"
    For j = 2 To CInt(Cells(Rows.Count, "A").End(xlUp).Row)
    If Range(Sheets("Full Log").Cells.Address).Cells(j, 1).Value = asnum Then
        If allAsNum Is Nothing Then
            Set allAsNum = Range(Sheets("Full Log").Cells.Address).Cells(j, 1)
        Else
            Set allAsNum = Union(allAsNum, Range(Sheets("Full Log").Cells.Address).Cells(j, 1))
        End If
    End If
    Next j
    
    Set currAsNum = allAsNum.Rows 'This is the line that I can't figure out
    currAsNum.Select

AdvancedFilter is a much more efficient way to copy data based on selection criteria. — Nicholas Hunter, Apr 01 '21 at 17:59

score 2 · Answer 1 · answered Apr 01 '21 at 15:41

Scott Craner is right. However, some remarks to your code

a) Being a beginner is no excuse for not indenting your code. Simply follow the rule to add 1 to the level of indentation at every Sub, If, For and With-statement (this list is not complete, but you get the idea). Subtract 1 at the matching End-statement. Use a <TAB> for every indent.

b) Do not use select. Obligatory link to How to avoid using Select in Excel VBA

c) You use the correct technique to get the last row. However, this returns already a Long value, no need to cast it using CInt. For debugging reason, it is better to write this into a variable before using it. You should, by the way, declare you variable j as Long (and maybe think about a better name).

d) Your technique to read a cell works, but is unnecessarily complicated. Simply use Cells(j, 1)

Code could look like this:

Const asnum = "searchingvalue"
Dim allAsNum As Range
Dim rowCount as long, curRow as long

With ThisWorkbook.Sheets("Full Log")   
    rowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
    For curRow = 2 To rowCount 
        If .Cells(curRow , 1).Value = asnum Then
            If allAsNum Is Nothing Then
                Set allAsNum = .Cells(curRow, 1)
            Else
                Set allAsNum = Union(allAsNum, .Cells(curRow, 1)) 
            End If
        End If
    Next curRow 
End With
' (The destination of the following copy needs to be adapted to your needs)
allAsNum.EntireRow.Copy ThisWorkbook.Sheets("Sheet1").Range("A1")

One note, that I failed to state. If using `EntireRow` your paste must be in Column `A`. The row can change but the column must be the first column or you will get an error as you cannot paste an entire row into a partial row. — Scott Craner, Apr 01 '21 at 15:55

Selecting the rows of each cell within a range object (VBA)

1 Answers1