CODEWITHSUNDEEP
excelvba
accortdr
accortdr

Reputation: 91

Selecting dynamic, filtered range in VBA

I am attempting to select a dynamic range of filtered data that spans from col. A: col. J without selecting the header (in row 1). From there I need to copy and paste it into a new sheet where I will manipulate it further, but I cannot come up with an efficient or functional way to do this. Based on some code I found on another forum I was able to select all of the "visable cells" in a single column, but I am running into issues trying to select the whole range. I am still very new to vba so forgive my syntax, but my code posted below was an attempt to itterate through Rows.Count and i which was an integer 1-10. If you have any advice on how to do this better and more efficiently I would really appreciate it.

Sub SelectVisibleInColD()
    Dim lRow As Long, i As Integer
    Set i = 1
Do While i <= 10

    With ActiveSheet
        lRow = .Cells(.Rows.Count, i).End(xlUp).Row
        If lRow < 3 Then Exit Sub
        .Cells(1, 1).Offset(1, 0).Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Select
    End With

 i = i + 1
 Loop
End Sub

Upvotes: 1

Views: 2364

Answers (2)

shadyTree
shadyTree

Reputation: 1

I came across this answer googling my issue for: deleting of filtered selection in vba. However trying your answer &lRow gives me an runtime error 1004, application-defineed or object-defined error

I got around it with this ActiveSheet.Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Delete

For those that may also get the same issue.

Upvotes: 0

CynicalSection
CynicalSection

Reputation: 704

You can select a range by using Range property of ActiveSheet. You already have the last row and you know that the header is in the first row, so your range starts from position A2 and goes to the last row of column J

ActiveSheet.Range("A2:J"&lRow).SpecialCells(xlCellTypeVisible)

If you want to copy this range, use Copy function like

yourRangeAsAbove.Copy

This function only moves the selection to memory, to paste it, build your destination range and call PasteSpecial function.

Upvotes: 2

Related Questions