How can I set the range from the active row down to last row?

Question

I want my For Loop to look through each row from (and including) the active row, down to the last row with data. It will then copy any rows in this range where the postcode is the same as the first active row, and paste these into a new workbook.

The original code uses the second row to the last row.

'Any extra measures need to be copied using an if loop,
'  this is so it will capture all rows underneath with the same postcode.

Dim r As Range
Dim j As Integer
Dim post_code_on_sheet As String
post_code_on_sheet = wb2.Worksheets("Sheet2").Cells(3, 2).Value
lastRow1 = wb.Worksheets("Sheet1").Range("J" & rows.Count).End(xlUp).row
j = 2
Cells(row, 10).Activate

'DEFINE RANGE AS FROM ACTIVE ROW DOWN TO LAST NON-EMPTY ROW
For Each r In wb.Worksheets("Sheet1").Range("J2:J" & lastRow1)
'This copies all of the rows that match within the whole workbook, 
' need it to select only from the active row down
   If r = post_code_on_sheet Then
       wb.Worksheets("Sheet1").rows(r.row).Copy wb2.Worksheets("Sheet1").rows(j)
       j = j + 1
    End If
Next r

I tried to adjust the range to go from the active cell row to the last row.

'Any extra measures need to be copied using an if loop,
' this is so it will capture all rows underneath with the same postcode.

Dim r As Range
Dim j As Integer
Dim post_code_on_sheet As String
post_code_on_sheet = wb2.Worksheets("Sheet2").Cells(3, 2).Value
lastRow1 = wb.Worksheets("Sheet1").Range("J" & rows.Count).End(xlUp).row
j = 2
Cells(row, 10).Activate

'DEFINE RANGE AS FROM ACTIVE ROW DOWN TO LAST NON-EMPTY ROW
For Each r In wb.Worksheets("Sheet1").Range(ActiveCell.row & lastRow1)
'This copies all of the rows that match within the whole workbook, need it to select only from the active row down
   If r = post_code_on_sheet Then
       wb.Worksheets("Sheet1").rows(r.row).Copy wb2.Worksheets("Sheet1").rows(j)
       j = j + 1
    End If
Next r

When I hover over the ActiveCell.row in the VBA window, it is showing the correct row number assigned to it and the lastRow1 variable is also correctly showing as 15.

Answer 1

A range for a certain amount of columns needs a start cell and an end cell, not two row numbers.

In your case, it'd be:

'Any extra measures need to be copied using an if loop, this is so it will capture all rows underneath with the same postcode.
'I want the range to be from the active cell, down to the last row. I am having trouble setting this.

Dim r As Range
Dim j As Long
Dim post_code_on_sheet As String
post_code_on_sheet = wb2.Worksheets("Sheet2").Cells(3, 2).Value
lastRow1 = wb.Worksheets("Sheet1").Range("J" & rows.Count).End(xlUp).row
j = 2
Cells(row, 10).Activate 'what is row here?
'if row isn't defined as a proper variable, this will throw an error
'DEFINE RANGE AS FROM ACTIVE ROW DOWN TO LAST NON-EMPTY ROW
For Each r In wb.Worksheets("Sheet1").Range("J" & row & ":J" & lastRow1)
'This copies all of the rows that match within the whole workbook, need it to select only from the active row down
   If r = post_code_on_sheet Then
       wb.Worksheets("Sheet1").rows(r.row).Copy wb2.Worksheets("Sheet1").rows(j)
       j = j + 1
    End If
Next r

If you only want the values, there's no need to copy paste though.

Edit: Didn't notice you got helped in the meantime, whoops.