Remove entire lines of data that includes "x"

dssprop

New Member
Joined
Jul 20, 2020
Messages
2
Office Version
  1. 365
For example can I highlight an entire section or even an entire page and state remove all line items that include the letter "Z" or that include a space
Example:
I put a command that says remove line items with a space. Line number 4 will be completely deleted because it has a space

1. cookiesnmilk
2. pbnjelly
3. chipsnsalsa
4. chips and salsa

Seems to me like that is possible. Does anyone have any suggestions on how to get that done?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,693
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Which column is the data in ??
 

dssprop

New Member
Joined
Jul 20, 2020
Messages
2
Office Version
  1. 365
All the data is in a vertical column could be ABCD... I am hoping I can just click and highlight the entire column and delete entire lines that contain a character space, The report I have shows a person first and last name on one line than the it shows their user name on the next line. So I am going through 1 by 1 and deleting the names because all I need in this report is user name.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,693
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok, you can use rhis method to decide which column you need to check, without the nneed to select that column
This will delete any row that has a space or a "Z" in the text
VBA Code:
Sub MM1()
Dim lr As Long, r As Long, c As String
c = Application.InputBox("Please Insert Column Letter to Search")
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
For r = lr To 1 Step -1
If InStr(Cells(r, c), " ") Or InStr(Cells(r, c), "Z") Then
    Rows(r).Delete
End If
Next r
End Sub
If you just want to check for a space ONLY use
VBA Code:
Sub MM1()
Dim lr As Long, r As Long, c As String
c = Application.InputBox("Please Insert Column Letter to Search")
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
For r = lr To 1 Step -1
 If InStr(Cells(r, c), " ") Then
    Rows(r).Delete
End If
Next r
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

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
Top