Defining Range using variable

Question

I have certain values in my column A.

For exapmple:

Header
A
A
C
C
D
D
E
F

I want to keep rows having D and remove all other. As a first step, Have sorted my sheet. Now trying below code but its giving error of

Invalid or unqualified reference

Dim strA As Range
Dim strB As Range

    Range("A:A").Select
    Selection.Find(What:="D", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    Set strA = .ActiveCell


    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, ActiveCell.Offset(1, 0).Select).Select
    ActiveCell.Offset(1, 0).Select
    Set strB = .ActiveCell

    Range(strA, strB).Select

End Sub

Number of rows are almost 2L+ and hence not keen to use filter — Dipal Shah, May 07 '20 at 12:35
Remove the `.` in front of each instance of `.ActiveCell`. Then read [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, May 07 '20 at 12:36
What does "2L+" mean and why does this mean you don't want to use a filter? — SJR, May 07 '20 at 12:45
@SJR - [Lakh notation](https://en.wikipedia.org/wiki/Lakh)... and I'd use a filter too. — BigBen, May 07 '20 at 12:45
@SJR since number of rows are above 2 lakh, using filter takes more time — Dipal Shah, May 07 '20 at 12:53

score 0 · Answer 1 · answered May 07 '20 at 15:25

Maybe something like this :

Sub test()
n = Application.WorksheetFunction.CountIf(Range("A:A"), "D")
Set c = Range("A:A").Find("D", lookat:=xlWhole)
Range("A" & c.Row, Range("E" & c.Row).Offset(n - 1, 0)).Copy Destination:=Range("A2") 'change E as needed, depends how many column is your data
Set rngDel = Range("A1").Offset(n + 1, 0)
Range(rngDel, rngDel.End(xlDown)).EntireRow.Delete
End Sub

Before running the code, the data table must be sort first by column A.
The code will count how many "D" are there in column A.
Then it get the cell where it find the first "D"
From there it copy all the cells which has "D" value
then paste it to cell A2
lastly, it delete the remaining cells which doesn't have "D" value

Defining Range using variable

1 Answers1