code to delete rows that cells are not highlighted

[newtoexce123]

need a macro that will delete rows in which cells are not highlighted, and then copy the results to another sheet. since data changes it can go to the last row of available data.

[Bernie Deitrick]

Just apply a filter and show or hide just the rows with the value of interest (or highlighting) and delete them or the rest. For example, filter your column F and un-check Living room - and delete everything else.

[newtoexce123]

thanks for the reply, i understand i can filter them out but i want it to become part of a bigger macro
that I'm trying to build which will make it completely automated. Either delete the rows or just import
the highlighted rows with highlighted results to a different sheet

[Bernie Deitrick]

I don't know if you want the highlighted cells moved or deleted....

[newtoexce123]

I want the Rows with the highlighted cells moved to another sheet

[beyond Excel]

thanks for the reply, i understand i can filter them out but i want it to become part of a bigger macro
that I'm trying to build which will make it completely automated. Either delete the rows or just import
the highlighted rows with highlighted results to a different sheet

Do we agree that the rows to filter are those with 'Living Room' in 'Location'?

[newtoexce123]

Do we agree that the rows to filter are those with 'Living Room' in 'Location'?

Yes, the entire row with the highlighted text in location will be moved

[beyond Excel]

Hello.
I assume your workbook has two sheets: Sheet1 and Sheet2:

Sub Filter_Ok() 
Dim Txt$, a, iAdr$ 
Txt = "Living Room" 
With Range("Sheet1!A1").CurrentRegion 
  iAdr = .Columns("f").Address(external:=True) 
  a = Evaluate("Transpose(((" & iAdr & "=""Location"") + (" & iAdr & "=""" & Txt & """)) * Row(F1:F12))") 
  a = Filter(a, 0, False): If UBound(a) = -1 Then MsgBox "No records.": End 
  a = Application.Index(.Cells, Application.Transpose(a), [{1,2,3,4,5,6}]) 
End With 
Range("Sheet2!A1").CurrentRegion.Delete xlShiftUp 
Range("Sheet2!A1").Resize(UBound(a), UBound(a, 2)) = a 
End Sub  

[newtoexce123]

Thank you ! that worked perfectly for the provided example! How would i go about changing the code to where
it would move the rows in the highlighted cells in the attached excel file

[beyond Excel]

Look: it seems to me that we are not going to the bottom of the problem, that is:

What is the criteria you are using to highlight some cells yes and other cells no?

> Instead of highlighting cells (an otherwise ineffective criterion): Could you include an auxiliary column marking in some way what you want to filter and copy?...

[Sintek]

code where it would move the rows in the highlighted cells

Please Login or Register  to view this content.

[newtoexce123]

Please Login or Register  to view this content.

Thank you!! this is exactly what i was looking for and i was able to manipulate it to what i needed do !

[Sintek]

Glad to have contributed...Tx for rep +