I want delete all the rows that matches specific column value in excel using vba but facing error

Question

Sub Delete_Rows()

Dim lo As ListObject 
Dim ActiveSheet As String
Dim wkbSource As Workbook

Application.ScreenUpdating = False

Set wkbSource = Workbooks.Open("C:\Users\nani\Desktop\11.0\deleteRows\abc.xlsx")

Set ActiveSheet = wkbSource.Sheets("LIST")

'perform delete

Set lo = ActiveSheet.ListOjects(1)

lo.Range.AutoFilter Field:=12, Criteria:="" Or "claimed"

Application.DisplayAlerts = False

lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete

Application.DisplayAlerts = True

wkbSource.Close SaveChanges:=True

End Sub

What is the text of the error message, and on which line of code? — Tim Williams, May 18 '20 at 05:46
and also i want to check 2 more colums 13 and 14 and need to delete all rows satisfying all 3 columns conditions — Nani, May 18 '20 at 05:53
ActiveSheet should be declared as an object and not as a string — SmIqbal, Jul 10 '20 at 11:00

score 0 · Answer 1 · answered May 18 '20 at 05:52

0

Some fixes:

Sub Delete_Rows()

Dim lo As ListObject 
Dim ws As Worksheet '<<<<<<
Dim wkbSource As Workbook

Application.ScreenUpdating = False

Set wkbSource = Workbooks.Open("C:\Users\nani\Desktop\11.0\deleteRows\abc.xlsx")

Set ws = wkbSource.Sheets("LIST")

Set lo = ws.ListObjects(1)  '<< typo

'fixed...
lo.Range.AutoFilter Field:=12, Criteria1 := "=", _
                           Operator := xlOr, _
                           Criteria2 := "claimed"

Application.DisplayAlerts = False

lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete

Application.DisplayAlerts = True

wkbSource.Close SaveChanges:=True

End Sub

answered May 18 '20 at 05:52

Tim Williams

154,628
8
97
125

Is it correct? lo.Range.AutoFilter Field:=12, Criteria1 := "=", _ Operator := xlOr, _ Criteria2 := "claimed" lo.Range.AutoFilter Field:=13, Criteria3 := "=", _ lo.Range.AutoFilter Field:=14, Criteria1 := "=", _ ' <<<< if column value empty – Nani May 18 '20 at 06:00
There's no `Criteria3` - that should be `Criteria1` – Tim Williams May 18 '20 at 06:02
line 9 Set lo = ws.ListObjects(1) '<< typo error – Nani May 18 '20 at 06:04
subscript out of range – Nani May 18 '20 at 06:05
There's no listobject/table on that sheet? – Tim Williams May 18 '20 at 06:06
we have header rows and columns – Nani May 18 '20 at 06:07
Is it an actual Table/ListObject though, or just a set of data? https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c – Tim Williams May 18 '20 at 06:14
just a set of data with filter enabled on columns – Nani May 18 '20 at 06:16
...but you can convert it to a table? You need to do that if you want to use `DataBodyRange` – Tim Williams May 18 '20 at 06:18
but i dono how much column range will be – Nani May 18 '20 at 06:20
i have an excel file in which we have 1 sheet with A to N columns. I need to filter L columns with empty or "test" and M column not equal to empty and N column not equal to empty and delete all the rows satisfying above conditions – Nani May 18 '20 at 06:36
Did you try the code I posted? You only need to convert your data into a Table – Tim Williams May 18 '20 at 06:40
line 9 Set lo = ws.ListObjects(1) subscript out of range error – Nani May 18 '20 at 06:41

I want delete all the rows that matches specific column value in excel using vba but facing error

1 Answers1