macro- delete hidden rows after filter in place

[zorrka0990]

Hello all
first time user but have used this board many times. macro noob here but have been doing basic macros for a while.

I have 2 tabs, tab 1 and tab 2
They both have same content
let's talk about tab 1 which has the following data

Fund	dept	Descrip	Item
123	abc	des	c1
431	abc	des	c1
431	abc	des	c2
123	abc	des	c2
123	abc	des	c4
431	abc	des	c5

<tbody>
</tbody>

My first macro populates the in filter criteria on column F and I, since I want to filter fund and Item [A & D]. This works well [basically hardcoding the values into the fields F2, I2,I3

My in filter criteria

Fund	Dept	Descrip	Item
123			c1
			c5

<tbody>
</tbody>

What needs to happen is that , I need to first filter by 123, then delete all hidden lines and then filter by c1 and c5. This should ultimately only give me , one row at the end

let me tell you all that I have tried [mind I am not a pro at excel]

1. I tried filtering col A with the values in col F. That gives me [This works ok]

Fund	Dept	Descrip	Item
123	abc	des	c1
123	abc	des	c2
123	abc	des	c4

<tbody>
</tbody>

2. Then I try to delete the hidden rows [which do not work]
Also please not I am only trying to delete the first four columns, whereas most of the macros I see online delete the full row. This is a problem because it could delete my filter criteria in columns F and I

3. Next I have to filter col D with values in I. what I should get is the following

Fund	Dept	Descrp	Item
123	abc	des	c1

<tbody>
</tbody>

issues I am having
.
' start sheet1 criteria filtering for FUND

With ActiveSheet.Range("A:N")
Range("A:N").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("R1:R2"), Unique:=False

'start Sheet1 criteria filtering for Item


Columns("A:N").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("U1:U11"), Unique:=False

in above code I am first selecting A to N and then i have my filter in R1 and R2.

I used a few different snipps from the web and they dont work. Predominantly they delete the whole line which wipes out the second filter that's sitting in U1 to U11.
Also after second filter is run , all the original items from column A are back again [Kinda like the first round of filtering never happened]


One other option I tried was to find the last line of data and then enter the filtering data below [to prevent filter data from getting wiped out]
The problem there is i do not know again how to filter
Range("A:N").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("R1:R2"),

because each time I run for a month, my last line will change, how can i set the range to Range(RLastline +1:Rlastline +2)
for ex , one month last line can be 100, so filter range could be Range(R101:R102) and next month filter range could be Range(R201:R202)

let me know your thoughts? Thanks

 

[PhilS2520]

Hello zorrka0990,

Have you tried to record these actions that you are doing, and look at the code generated and clean it up to make it work for you?

I would suggest this route to learn more about all the VBA code the recorder can generate, and then how to clean it up.

Hope this helps.

 

[zorrka0990]

Hello zorrka0990,

Have you tried to record these actions that you are doing, and look at the code generated and clean it up to make it work for you?

I would suggest this route to learn more about all the VBA code the recorder can generate, and then how to clean it up.

Hope this helps.

Thanks for replying. yeah I recorded , the problem is that of deleting the hidden columns. Anyway I have used a different approach and instead of using an advanced filter, i just filtered in place and it pretty much gives me what I want.

But still any tips on deleting hidden rows will be very helpful

 

[zorrka0990]