Deleting rows

[azizrasul]

I want to delete rows based values other than specific values. Here's the code I am using which gives me an error on the .Delete line.

Set ws = ThisWorkbook.Sheets("tblComputerSystemInformation")

With ws.Range("C2:C" & WorksheetFunction.CountA(Range("tblComputerSystemInformation[nametype]")))
        .AutoFilter Field:=4, Criteria1:="Not In ('OSArchitecture','Name','OSLanguage')"
        .SpecialCells(xlCellTypeVisible).Delete
End With

In MS Access you can use criteria like

Not In ('OSArchitecture','Name','OSLanguage')

Is it possible to do something similar in MS Excel?

 

[xlnab]

Hey there,

Create a copy of your workbook (it's always good to do this, VBA can make irreversible damage to a xl file and data) and give this code bellow a try.

.EntireRow.Delete
Instead of
.Delete

Set ws = ThisWorkbook.Sheets("tblComputerSystemInformation")


With ws.Range("C2:C" & WorksheetFunction.CountA(Range("tblComputerSystemInformation[nametype]")))

        .AutoFilter Field:=4, Criteria1:="Not In ('OSArchitecture','Name','OSLanguage')"

        .SpecialCells(xlCellTypeVisible).EntireRow.Delete

End With

 

[azizrasul]

Hey there,

Create a copy of your workbook (it's always good to do this, VBA can make irreversible damage to a xl file and data) and give this code bellow a try.

.EntireRow.Delete
Instead of
.Delete

Set ws = ThisWorkbook.Sheets("tblComputerSystemInformation")


With ws.Range("C2:C" & WorksheetFunction.CountA(Range("tblComputerSystemInformation[nametype]")))

        .AutoFilter Field:=4, Criteria1:="Not In ('OSArchitecture','Name','OSLanguage')"

        .SpecialCells(xlCellTypeVisible).EntireRow.Delete

End With

 

[azizrasul]

I get the error 'No cells were found' on the .EntireRow.Delete line.

 

[xlnab]

Oh I see,
That is a macro to filter values right?
Can you provide the file so we can take a look?

 

[azizrasul]

Oh I see,
That is a macro to filter values right?
Can you provide the file so we can take a look?

 

[azizrasul]

How do I upload the file?

 

[xlnab]

Using a online upload service like dropbox or something like that I belive.

 

[azizrasul]

Using a online upload service like dropbox or something like that I belive.

Do you want to give me your email address so that I can add it to my dropbox and you can then download the file.

 

[MARK858]

You mark the file for sharing in your dropbox and then post the link it provides in the thread.