macro- delete hidden rows after filter in place

zorrka0990

New Member
Joined
Jun 20, 2018
Messages
3
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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Fund
[/TD]
[TD]dept
[/TD]
[TD]Descrip
[/TD]
[TD]Item
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]abc
[/TD]
[TD]des
[/TD]
[TD]c1
[/TD]
[/TR]
[TR]
[TD]431
[/TD]
[TD]abc
[/TD]
[TD]des
[/TD]
[TD]c1
[/TD]
[/TR]
[TR]
[TD]431
[/TD]
[TD]abc
[/TD]
[TD]des
[/TD]
[TD]c2
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]abc
[/TD]
[TD]des
[/TD]
[TD]c2
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]abc
[/TD]
[TD]des
[/TD]
[TD]c4
[/TD]
[/TR]
[TR]
[TD]431
[/TD]
[TD]abc
[/TD]
[TD]des
[/TD]
[TD]c5
[/TD]
[/TR]
</tbody>[/TABLE]


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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Fund
[/TD]
[TD]Dept
[/TD]
[TD]Descrip
[/TD]
[TD]Item
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD][/TD]
[TD][/TD]
[TD]c1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]c5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Fund
[/TD]
[TD]Dept
[/TD]
[TD]Descrip
[/TD]
[TD]Item
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]abc
[/TD]
[TD]des
[/TD]
[TD]c1
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]abc
[/TD]
[TD]des
[/TD]
[TD]c2
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]abc
[/TD]
[TD]des
[/TD]
[TD]c4
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Fund
[/TD]
[TD]Dept
[/TD]
[TD]Descrp
[/TD]
[TD]Item
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]abc
[/TD]
[TD]des
[/TD]
[TD]c1
[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
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

lxZY3EN
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top