VBA Select range with fixed width, but variable length

[mrMozambique]

Greetings, all. I have a macro that I recorded to copy a range from a sheet (Districts) and paste the values into a series of other sheets. The range is fixed width (2 columns), but can grow or shrink in length based on a filter I have set up in adjacent columns. So, the process is that I filter column C for Not Null and then copy and paste the resulting contents from columns A and B into another sheet (PP).

When I record the Macro I use the XLDown to select the length, but I can't use XLRight because it will extend to column C or beyond. The macro has an absolute Range selection (A4:B8). When I add a new district and run the macro, it doesn't capture it because it's in row 9.

My question is how to I make the selection of my range relative so it will grow as my data grow? Any help is greatly appreciated.

Application.Goto Reference:="community"
    ActiveSheet.Unprotect
    ActiveSheet.Range("$A$3:$G$202").AutoFilter Field:=3, Criteria1:="<>"
    Range("A4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A4:B8").Select
    Sheets("PP").Select
    ActiveSheet.Unprotect
    Range("B5").Select
    Sheets("Districts").Select
    Selection.Copy
    Sheets("PP").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True
    Sheets("Districts").Select
    ActiveSheet.Range("$A$3:$G$202").AutoFilter Field:=3

 

[Weaver]

Range("A4",cells(rows.count,"B").end(xlup).select

Although you don't need to select cells to do things with them

Range("A4",cells(rows.count,"B").end(xlup).copy

for instance.

Plus, you might need to look at specialcells(xlcelltypevisible)

HTH

 

[mrMozambique]

Thanks, Weaver. I ended up with the following which is working excellently. I forgot that row 4 could also be filtered at some point so I used the Cells(3+1,1) with the xlCellTypeVisible function as you suggested. I cleaned it up quite a bit as well to avoid the normal macro recording rubbish.

    wsDISTRICTS.Range("Community").AutoFilter Field:=3, Criteria1:="<>"
    wsDISTRICTS.Range(Cells(3 + 1, 1), Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
    wsPP.Range("b5:c203").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    wsDISTRICTS.Select
    wsDISTRICTS.Range("Community").AutoFilter Field:=3

 

[Weaver]

That's good, but cells(3+1,1) is still the same as cells(4,1)

No doubt the specialcells is helping.

Best thing about avoiding all the selecting is you can work on different tabs and workbooks without switching focus and it's a lot quicker. I still use the macro recorder a lot, if only to avoid having to learn all the syntax structures!