PDA

View Full Version : Delete rows by cell value



ljlundgren
07-07-2017, 12:04 PM
I have an input sheet (sheet2) that has a text value in cell J6.
I want a loop that searches all of column B in sheet 3 for cells matching J6, and if it finds these cells to delete those rows.

For some reason I cannot quite figure it out...

Help would be appreciated

Thanks!

mdmackillop
07-07-2017, 12:29 PM
Use an Autofilter to select the rows and delete the rows. If you need help with this, let us know

ljlundgren
07-07-2017, 12:47 PM
Use an Autofilter to select the rows and delete the rows. If you need help with this, let us know

Thanks, to clarify, I do not want the data filtered out - I was hoping there was a way to delete the rows without filtering with a loop

mdmackillop
07-07-2017, 01:43 PM
Sub test()
With Sheet3.Columns(2)
.Cells(1).EntireRow.Insert
.AutoFilter 1, Sheet2.Range("J6")
.SpecialCells(12).EntireRow.Delete
End With
End Sub

ljlundgren
07-07-2017, 02:35 PM
Sub test()
With Sheet3.Columns(2)
.Cells(1).EntireRow.Insert
.AutoFilter 1, Sheet2.Range("J6")
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub



Thanks! That worked for the most part... my only issue is the top two rows can't be deleted and I don't want it selecting those two rows but since it selects all visible it deletes them as well. Is there a way to only select row 3 and down?

mdmackillop
07-07-2017, 02:43 PM
I don't get that behaviour. Can you post your workbook.

ljlundgren
07-11-2017, 09:40 AM
I have a range of values on sheet one (Ticker) and I want a loop that will go through that range and delete cells that match the text value in cell J6.

Is a loop the the best way to do this? I know it may be obvious but I am lost.

Thanks

mdmackillop
07-11-2017, 10:15 AM
Post #6?

ljlundgren
07-12-2017, 08:10 AM
Sorry, I did figure out the first post but this is slightly different. This data cannot be filtered or it will ruin my input sheet.
Im not sure if I can explain it any better but I have a cell (J6) containing a value of three letters that will frequently changes because it is an input cell. I also have a range, lets call it Ticket that is column D of that same worksheet. Is there a way without filtering these cells that it can search through this ticker range for a match of cell J6 and delete that cell? The reason I cannot use filters is it will ruin my formatting of my input page.

This problem is different than my initial post but very similar. With my initial post I was able to use filters on that sheet but with this sheet I cannot use filters or it will screw up my formatting.

Thanks for for your time

mdmackillop
07-12-2017, 08:58 AM
Please post and mark when threads are solved. It is helpful for other if you post your own solution if it differs.


Sub Test()
Dim i As Long
With Sheet1
For i = .Cells(Rows.Count, 4).End(xlUp).Row To 1 Step -1
If .Cells(i, 4) = .Range("J6") Then .Cells(i, 4).Delete shift:=xlUp
Next
End With
End Sub