What is the best way to erase lines in a table that are hidden

Romano_odK

Active Member
Joined
Jun 4, 2020
Messages
380
Office Version
  1. 365
Platform
  1. Windows
Good morning,
At first I searched through the internet for VAB coding to erase lines that are hidden by a filter. But the most of what I found are to complex for me to change or do not work properly. I simply only want to erase the lines that are hidden in a table. Who can advice me in this matter?
Thank you for you time.

Book1
ABCDEFGHIJK
1ArtikelcodeOmschrijvingKostprijsOntvgstdatumAantalWaardeVrrdrekStd_locatieMagazijnLocatieVoorraad
2100000Vito Glaserfix 111 6x2 mm wit - 10x25 m2,0011/07/202396103901P02B1P02B8,0
3100001Vito Glaserfix 111 6x2 mm zwart - 10x25 m2,0018/04/202396103901P04B1P04B8,0
4100002Vito Glaserfix 111 6x3 mm wit - 10x25 m2,0011/07/202396103901P02B1P02B8,0
5100003Vito Glaserfix 111 6x3 mm zwart - 10x25 m2,0025/04/202396103901P04B1P04B8,0
6100004Vito Glaserfix 111 6x4 mm wit - 10x25 m2,0006/09/202396103901P02B1P02B8,0
7100004Vito Glaserfix 111 6x4 mm wit - 10x25 m2,0006/09/202396103901P02B1P02C8,0
8100005Vito Glaserfix 111 6x4 mm zwart - 10x25 m2,0031/03/202196103901AAP1P04B8,0
9100006Vito Glaserfix 111 9x2 mm wit - 10x25 m2,0013/09/202360103901P01B1P01B8,0
10100007Vito Glaserfix 111 9x2 mm zwart - 10x25 m2,0016/08/202360103901P03B1P03B8,0
11100008Vito Glaserfix 111 9x3 mm wit - 10x25 m2,0016/08/2023200103901Q07A1Q07A8,0
12100008Vito Glaserfix 111 9x3 mm wit - 10x25 m2,0016/08/2023200103901Q07A1Q09B8,0
14100009Vito Glaserfix 111 9x3 mm zwart - 10x25 m2,0001/08/2023200103901Q07A1Q07B8,0
16100009Vito Glaserfix 111 9x3 mm zwart - 10x25 m2,0001/08/2023200103901Q07A1Q07A8,0
17100010Vito Glaserfix 111 9x4 mm wit - 10x25 m2,0027/06/2023200103901Q08A1Q07B8,0
18100010Vito Glaserfix 111 9x4 mm wit - 10x25 m2,0027/06/2023200103901Q08A1Q08A8,0
19100011Vito Glaserfix 111 9x4 mm zwart - 10x25 m2,0004/04/2023200103901Q07A1Q07A8,0
20100012Vito Glaserfix 111 9x5 mm wit - 10x10 m2,0023/08/202360103901P01B1P01B8,0
21100013Vito Glaserfix 111 9x5 mm zwart - 10x10 m2,0015/06/202160103901P03B1P03B8,0
22100014Vito Glaserfix 111 9x6 mm wit - 10x10 m2,0006/10/202060103901P01B1P01B8,0
23100015Vito Glaserfix 111 9x6 mm zwart - 10x10 m2,0018/04/202360103901P03B1P03B8,0
24101000Vito Glaserfix 111 9x1 mm wit - 33 m2,0023/05/20232448103901O01A1O01A8,0
25101000Vito Glaserfix 111 9x1 mm wit - 33 m2,0023/05/20232448103901O01A1O01C8,0
26101001Vito Glaserfix 111 9x1 mm zwart - 33 m2,0011/07/20232473103901O01A1O01A8,0
27101001Vito Glaserfix 111 9x1 mm zwart - 33 m2,0011/07/20232473103901O01A1O01B8,0
28
Sheet1
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
You can try this vba code on a copy of the sheet :

VBA Code:
Sub DeleteHiddenRows()

Dim LastRow As Long

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

For iCntr = LastRow To 1 Step -1

    If Rows(iCntr).Hidden = True Then Rows(iCntr).EntireRow.Delete

Next

End Sub
 
Upvote 0
You can try this vba code on a copy of the sheet :

VBA Code:
Sub DeleteHiddenRows()

Dim LastRow As Long

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

For iCntr = LastRow To 1 Step -1

    If Rows(iCntr).Hidden = True Then Rows(iCntr).EntireRow.Delete

Next

End Sub
Works great, thank you for your reply, but it isnt the fastest for 5000 lines I have. Have a great day.
 
Upvote 0
What rule/s are you using for the autoilter?
 
Upvote 0
Yes but are you using rules like greater than, matches etc. or are you manually selecting the checkboxes to filter on?
What I am thinking is if it is a set criteria then it would be a lot faster to reverse the criteria and use SpecialCells (xlVisible) rather than looping through the hidden cells
 
Upvote 0
Try this:
VBA Code:
Sub Romano_odK()
Dim r As Range, q As Range
Application.ScreenUpdating = False
Set q = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set r = q.SpecialCells(xlCellTypeVisible)

With q
    .AutoFilter
    r.EntireRow.Hidden = True
    .SpecialCells(xlCellTypeVisible).EntireRow.ClearContents
    .EntireRow.Hidden = False
    .Resize(, 11).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlYes
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this:
VBA Code:
Sub Romano_odK()
Dim r As Range, q As Range
Application.ScreenUpdating = False
Set q = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set r = q.SpecialCells(xlCellTypeVisible)

With q
    .AutoFilter
    r.EntireRow.Hidden = True
    .SpecialCells(xlCellTypeVisible).EntireRow.ClearContents
    .EntireRow.Hidden = False
    .Resize(, 11).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlYes
End With
Application.ScreenUpdating = True
End Sub
Good morning,
This is very fast but also erases my table and does not delete the lines. Fast yes, but not as functional as I would like it to be. Thank you for our time.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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