Macro

[DMFisher]

Hi,

Is it possible to have a macro that deletes data from a table?

e.g - If cell I10 (Sheet2) matches data in a table on Sheet1 then delete the row that the data is stored in on Sheet1

 

[SteveO59L]

something along the lines of

Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = Lastrow To 1 Step -1
If Cells(i, 1).Value = Sheets("Sheet2").Range("i10").Value Then
Rows(i).Delete
End If
Next

 

[DMFisher]

Hi Steve,

If I wanted the macro to search for the data typed in I10 (on sheet 2) in column B on sheet 1, (Then delete that row) what would this code look like?

 

[SteveO59L]

Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "b").End(xlUp).Row
For i = Lastrow To 1 Step -1
If Cells(i, 2).Value = Sheets("Sheet2").Range("i10").Value Then
Rows(i).Delete
End If
Next

 

[DMFisher]

This doesn't seem to work. I dont think I explained it very well previously + I had the wrong cell

The formula basically needs to do the following:

If cell D9 on Sheet1 matches a cell in Column B on Sheet2, then delete the row that contains that data from sheet2

 

[SteveO59L]

sheets("Sheet2").activate
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "b").End(xlUp).Row
For i = Lastrow To 1 Step -1
If Cells(i, 2).Value = Sheets("Sheet1").Range("d9").Value Then
Rows(i).Delete
End If
Next

 

[DMFisher]

Thanks!

 

[DMFisher]

This macro has worked but when I use it on a different sheet (And change the sheet numbers in the macro) the 6th row is highlighted as incorrect when I try to run it.:
If Cells(i, 2).Value = Sheets("Sheet3").Range("d9").Value Then

 

[SteveO59L]

is the name of the sheet (exactly) Sheet3 ?

 

[DMFisher]

Yes