PDA

View Full Version : Solved: How to Delete Data on the other sheet using VBA?



genracela
04-19-2010, 12:52 AM
I have a VBA code for deleting data:


Sub DeleteData()
Dim LR As Long
LR = Range("J" & Rows.Count).End(xlUp).Row
On Error Resume Next
Range("J4:J" & LR).SpecialCells(xlBlanks, xlTextValues).EntireRow.Delete
On Error GoTo 0
End Sub


But this code deletes blank rows on this sheet.

What if I want to delete the data on the other sheet?

I tried modifying the formula but this doesn't work.


Sub DeleteData()
Dim LR As Long
Sheets("sheet2").LR = Range("J" & Rows.Count).End(xlUp).Row
On Error Resume Next
Range("J4:J" & LR).SpecialCells(xlBlanks, xlTextValues).EntireRow.Delete
On Error GoTo 0
End Sub


Please help.

Aussiebear
04-19-2010, 02:14 AM
Are you trying to delete column J on sheet 2? and what triggers this event?

mdmackillop
04-19-2010, 05:27 AM
You have to make a reference to the other sheet whenever you deal with a range
Sub DeleteData()
Dim LR As Long
With Sheets("sheet2")
LR = .Range("J" & Rows.Count).End(xlUp).Row
On Error Resume Next
.Range("J4:J" & LR).SpecialCells(xlBlanks, xlTextValues).EntireRow.Delete
On Error GoTo 0
End With
End Sub

genracela
04-19-2010, 04:40 PM
Thanks mdmckillop! By doing this I also resolved my other issue "How to combine multiple codes in 1 sheet"!

Thanks a million! I hope you'll not go tired of helping people like me!:bow: