CODEWITHSUNDEEP
excelvbaautofilter
user3259118
user3259118

Reputation:

Apply Autofilter to separate ‘blocks’ of data on same sheet

So I have a problem using autofilter. I have a sheet (sheet1) of data that is imported from elsewhere that gives me 3 separate ‘blocks’ of data in columns A to D comprising 5-12 rows of data each. There is a ‘gap’ of 3 empty rows in between each block. I apply an autofilter using Criteria I get from sheet2 cell A2 which uses a dropdown of employees surnames.

I copied some code I found on this site on how to apply autofilter, but the problem is it only applies the filter to the first ‘block’ of data and I want it to apply to the whole sheet to all 3 blocks at the same time.

Can you point out how I can fix my code?

Sub FilterByName()

myFilter = Sheets(2).Range("A2").Value

With Sheets(1).Range("A1").CurrentRegion
.Range("A1").AutoFilter Field:=2, Criteria1:=myFilter
End With


End Sub

Upvotes: 0

Views: 63

Answers (1)

user3259118
user3259118

Reputation:

CurrentRegion defines the contiguous range you start your code on – that’s where your problem lies. You could find the last row of data in Sheet1 and define the whole range to filter, alternatively you can actually apply AutoFilter to an entire column – which will ignore blank rows. According to your code, you’re filtering on column B. Please try the following & let me know how it goes.

Option Explicit
Sub FilterColumn()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)

Dim myFilter As String
myFilter = ws2.Cells(2, 1).Value2

ws1.Columns(2).AutoFilter 1, myFilter

End Sub

Upvotes: 1

Related Questions