Auto filter and delete blank rows

[pwill]

Hi can anyone help?

On sheet1 I have data
A1:G1 have headers A B C D E F G
A2:H29 has data

I have recored a macro and added (lRow) to the code to delete blank rows and unwanted data using AutoFilter. I want to Select ("A2:G" & lRow) at the end of the code which should select "A2:G11" (the filtered data) but for some reason it selects A2:G29

Can anyone explain why it does this and how I can fix it?

This is my code and worksheet example any help would be apprieciated

Regards

pwill

Sub FilterData()

    Dim wsSht As Worksheet: Set wsSht = Sheet1
    Dim lRow As Long
    
    lRow = wsSht.Cells(Rows.Count, "A").End(xlUp).Row

    Range("G2:G" & lRow).Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:G" & lRow).AutoFilter
    Range("A2:G" & lRow).AutoFilter Field:=7, Criteria1:="="
    Range("A3:G" & lRow).EntireRow.Delete
    Selection.AutoFilter
    
    Range("A2:G" & lRow).Select
    
End Sub

eg

	A	B	C	D	E	F	G	H	I	J	K
1	A	B	C	D	E	F	G
2	1	2	3	4	5	6		7
3
4	01/01/18
5	1	2	3	4	5	6		7
6
7	01/01/18
8	1	2	3	4	5	6		7
9
10	01/01/18
11	1	2	3	4	5	6		7
12
13	01/01/18
14	1	2	3	4	5	6		7
15
16	01/01/18
17	1	2	3	4	5	6		7
18
19	01/01/18
20	1	2	3	4	5	6		7
21
22	01/01/18
23	1	2	3	4	5	6		7
24
25	01/01/18
26	1	2	3	4	5	6		7
27
28	01/01/18
29	1	2	3	4	5	6		7

<colgroup><col style="width:48pt" span="3" width="64"> <col style="width:48pt" span="5" width="64"> <col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>

 

[Fluff]

It does that because you haven't reset the value of lRow after deleting the blank rows

 

[pwill]

Thanks Fluff for your reply do you know how I reset the value?

regards

 

[Fluff]

The same way you set it initially

 

[pwill]

Do you mean add another lastRow to the code?

eg

Sub FilterData()

    Dim wsSht As Worksheet: Set wsSht = Sheet1
    Dim lRow As Long
    Dim lastRow As Long
    
    lRow = wsSht.Cells(Rows.Count, "A").End(xlUp).Row
    lastRow = wsSht.Cells(Rows.Count, "A").End(xlUp).Row

 

[Fluff]

No, you can't declare a variable more than once.
You just need to change the value it holds, so add this line

lRow = wsSht.Cells(Rows.Count, "A").End(xlUp).Row

just after you remove the autofilter

 

[pwill]

Got it

Thank you Fluff much apprieciated

regards

pwill

 

[Fluff]

Glad to help & thanks for the feedback