Quick n' dirty code to delete rows?

[Guest]

Hello,

Can anyone write me a quick VBA example to delete an entire row if a
particular cell within that row is empty? I need excel to do this to multiple
rows until it gets to the end of the data.

Anyone?

 

[Harald Staff]

Sub Dirty()
Dim L As Long
For L = 200 To 1 Step -1
If Cells(L, 1).Formula = "" Then Rows(L).Delete
Next
End Sub

HTH. Best wishes Anyone

 

[Gord Dibben]

Public Sub DeleteRowOnCell()
''delete any row that has a blank in selected column
Set coltocheck = Application.InputBox(prompt:= _
"Select A Column", Type:=8)
coltocheck.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub


Gord Dibben Excel MVP

 

[Soo Cheon Jheong]

Hi,

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Sub TEST()

Dim RNG As Range

If TypeName(Selection) <> "Range" Then GoTo e:
If Selection.Areas.Count > 1 Then GoTo e:

LOOP_1:
On Error Resume Next
Set RNG = Application.InputBox(Prompt:="Select a Range", _
Default:=Selection.Address, Type:=8)
If Err.Number > 0 Then GoTo e:
On Error GoTo 0

If RNG.Cells.Count = 1 Then
If Len(RNG) = 0 Then RNG.EntireRow.Delete
Else
RNG.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If

e:

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


--
Reagrds,
Soo Cheon Jheong
_ _
^ ^
~

 

[Andy77]

You can be more detailed that this, but it can get you
going in the right direction.

'Understand that you must determine how many columns are
necessary to check

Sub Delete_Rows ()

r = 1
'Check each Row
Do
If Not IsEmpty(Cells(r,c)) Then
'Check each column within that row
Do Until c = 7 'Checks until the 7th column
If IsEmpty(Cells(r,c + 1)) Then
Cells(r,c).EntireRow.Delete
Else
c = c + 1
End If
Loop
End If 'Set up for next row
c = 1
r = r + 1
Loop Until IsEmpty(Cells(r,c))
'Assumes that at least column A will not be missing data.
End Sub


---Original Message-----