Excel - select end of column + extra rows

Question

I am trying to make a macro that selects certain data on my worksheet. I have a sheet with data that gets pulled into it using:

Windows("Item checkout workbook_New.xlsx").Activate
Range("A2:G300").Select
Selection.Copy
Windows("VLookup test.xlsx").Activate
Sheets("Sheet1").Select
Range("A2:G2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Sheets("Sheet1").Range("A2:G300").Copy Sheets("Sheet2").Range("A2")
Sheets("Sheet2").Select
Application.CutCopyMode = False

After entering this data, I have two columns H2:H300and I2:I300, which already have formulas for Vlookup, which get the information from A2:G300.

Then I need to select only the relevant data and copy it back to Windows("Item checkout workbook_New.xlsx"). For relevant data, I need to select only cells with data in the range A2:G300, as well as cells H2:I300that match. Having seen that ALL cells H2:I300have data, I'm not sure how to do this. I tried to create a macro that uses END to select the entire column A, and then the rows that go with it, but this is what I got, and as you can see, this will not work:

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:I78").Select
Selection.Copy

I'm not very good at VBA, so it's hard to figure things out on the fly, but I feel that there must be a way to make this work. Any advice would be great!

Answer 1

Range("A2").Select
Range(Selection, Selection.End(xlDown)).EntireRow.Select
Selection.Copy
Windows("Item checkout workbook_New.xlsx").Activate
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

It worked!

Answer 2

, , . , . . , . , . ( /)

.

Sub test()
    'store results here
    Dim result As Range

    setupTest
    'check this range and return items that are not empty
    selectMatchingCells Range("A1:D1"), result
    'check this range and return items that match value
    selectMatchingCells Range("B2:C4"), result, "hi"
    result.Select
End Sub

Function setupTest()
    Range("A1").Value = "anything"
    Range("c1").Value = "may go"
    Range("D1").Value = "here"
    Range("B2").Value = "hi"
    Range("B3").Value = "but not here"
End Function

Function selectMatchingCells(search As Range, result As Range, Optional searchValue As String = "")
    For Each cell In search
        'are we checking that cell value matches string, or if cell has a value at all?
        If searchValue = vbNullString Then
            'check if cell is not empty
            If IsEmpty(cell) = False Then selectCell result, cell
        Else
            'check if value matches
            If cell.Text = searchValue Then selectCell result, cell
        End If
    Next cell
End Function

Function selectCell(result As Range, cell As Variant)
    'check if result range already exists or not
    If result Is Nothing Then
        'make range equal to cell
        Set result = cell
    Else
        'add cell to existing range
        Set result = Union(result, cell)
    End If
End Function

, , , !