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?

 

[azizrasul]

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

Test.xlsm

Shared with Dropbox

www.dropbox.com

 

[azizrasul]

Here's the link to the file in dropbox.

Test.xlsm

Shared with Dropbox

www.dropbox.com

 

[MARK858]

I think to do what you want you will need to set up an Advanced Filter then run it via VBA or use Power Query(Get and Transform) , please see your other post and the response by @Fluff to see how to refer to a structured table in VBA.

Excel Advanced Filter Intro - Clear Steps, Videos, Examples

How to use Advanced Filter in Excel with criteria, send data to different sheet, or filter in place. Short videos, clear examples, written steps, workbook

www.contextures.com

 

[azizrasul]

I think to do what you want you will need to set up an Advanced Filter then run it via VBA or use Power Query(Get and Transform) , please see your other post and the response by @Fluff to see how to refer to a structured table in VBA.

Excel Advanced Filter Intro - Clear Steps, Videos, Examples

How to use Advanced Filter in Excel with criteria, send data to different sheet, or filter in place. Short videos, clear examples, written steps, workbook

www.contextures.com

Thanks, I will look into that.

 

[MARK858]

You're welcome, personally I would use Power Query (Get and Transform) if you have it (it would be useful if you updated your profile to show your Excel version and Operating system as it affects the answers people supply).

The M code for Power Query (Get and Transform) would look something like the below (the below only includes the criteria that was actually in your sheet as there were about 3 criteria that weren't in the sheet and I couldn't be bothered to manually add them).

let
    Source = Excel.CurrentWorkbook(){[Name="Table_tblComputerSystemInformation"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MotherBoardSerialNumber", type text}, {"groupname", type text}, {"nametype", type text}, {"valuename", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [nametype] = "OSArchitecture" or [nametype] = "Name" or [nametype] = "OSLanguage" or [nametype] = "RegisteredUser" or [nametype] = "SerialNumber" or [nametype] = "SystemDirectory" or [nametype] = "SizeStoredInPagingFiles" or [nametype] = "Manufacturer" or [nametype] = "Name" or [nametype] = "SerialNumber" or [nametype] = "Caption" or [nametype] = "Description" or [nametype] = "CSDVersion" or [nametype] = "ProcessorId" or [nametype] = "FileSystem" or [nametype] = "VolumeSerialNumber")
in
    #"Filtered Rows"

 

[azizrasul]

You're welcome, personally I would use Power Query (Get and Transform) if you have it (it would be useful if you updated your profile to show your Excel version and Operating system as it affects the answers people supply).

The M code for Power Query (Get and Transform) would look something like the below (the below only includes the criteria that was actually in your sheet as there were about 3 criteria that weren't in the sheet and I couldn't be bothered to manually add them).

let
    Source = Excel.CurrentWorkbook(){[Name="Table_tblComputerSystemInformation"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MotherBoardSerialNumber", type text}, {"groupname", type text}, {"nametype", type text}, {"valuename", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [nametype] = "OSArchitecture" or [nametype] = "Name" or [nametype] = "OSLanguage" or [nametype] = "RegisteredUser" or [nametype] = "SerialNumber" or [nametype] = "SystemDirectory" or [nametype] = "SizeStoredInPagingFiles" or [nametype] = "Manufacturer" or [nametype] = "Name" or [nametype] = "SerialNumber" or [nametype] = "Caption" or [nametype] = "Description" or [nametype] = "CSDVersion" or [nametype] = "ProcessorId" or [nametype] = "FileSystem" or [nametype] = "VolumeSerialNumber")
in
    #"Filtered Rows"

I have never used a power query before. I tried to copy and paste the code behind a command button on a form and the code all went red.

 

[MARK858]

It isn't VBA, it is M code...

Select your entire table, Click Data tab, click from table or range, click Advanced Editor, paste the code I posted over all the code you see, click done, click close and load, click close and load to, choose new sheet and click ok.

The new sheet is the data you want, if you need to update the info then click refresh all.

 

[azizrasul]

Great, thanks it worked.

 

[MARK858]

You're welcome (just remember that you manually need to add the 3 criteria to the code that weren't in the table).

 

[azizrasul]

Great, thanks it worked.

Am I right in saying that if the Excel file was given to someone else the Power Query AddIn would automatically be present or would they, assumming it isn't there, have to include the addIn or download it just as I had to do?