Converting a “Delete Column” Macro to a “Delete Row” Macro

Prev: Sorting help !!!!!!!!!!!!!!!!!
Next: Get a list of open workbooks and choose one

From: Wouter HM on 13 May 2010 15:53

---

Hi there

You are close.

In de code fragment Cells(a,b) the a refers to the row number and the
b refers to the column number.

Try:


Sub DeleteRows()
'
' DeleteRows Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Dim WS As Worksheet
Dim DeleteThese As Range
Dim LastRow As Long
Dim R As Long


For Each WS In _
Application.ActiveWindow.SelectedSheets
Set DeleteThese = Nothing
With WS
LastRow = .Cells(.Rows.Count, 1) _
.End(xlUp).Row
For R = LastRow To 1 Step -1
If .Cells(R, 1).Value = "DELETE" Then
If DeleteThese Is Nothing Then
Set DeleteThese = .Rows(R)
Else
Set DeleteThese = _
Application.Union(DeleteThese, .Rows(R))
End If
End If
Next R
If Not DeleteThese Is Nothing Then
DeleteThese.Delete
End If
End With
Next WS
End Sub


HTH,

Wouter

From: cardan on 13 May 2010 20:03

---

On May 13, 12:53 pm, Wouter HM <wouter.ma...(a)sogeti.nl> wrote:
> Hi there
>
> You are close.
>
> In de code fragment Cells(a,b) the a refers to the row number and the
> b refers to the column number.
>
> Try:
>
> Sub DeleteRows()
> '
> ' DeleteRows Macro
> '
> ' Keyboard Shortcut: Ctrl+Shift+F
> '
> Dim WS As Worksheet
> Dim DeleteThese As Range
> Dim LastRow As Long
> Dim R As Long
>
> For Each WS In _
>     Application.ActiveWindow.SelectedSheets
>     Set DeleteThese = Nothing
>     With WS
>         LastRow = .Cells(.Rows.Count, 1) _
>                 .End(xlUp).Row
>         For R = LastRow To 1 Step -1
>             If .Cells(R, 1).Value = "DELETE" Then
>                 If DeleteThese Is Nothing Then
>                     Set DeleteThese = .Rows(R)
>                 Else
>                     Set DeleteThese = _
>                         Application.Union(DeleteThese, .Rows(R))
>                 End If
>             End If
>         Next R
>         If Not DeleteThese Is Nothing Then
>             DeleteThese.Delete
>         End If
>     End With
> Next WS
> End Sub
>
> HTH,
>
> Wouter

Hello. Thanks for the reply. I am still getting errors with this
macro. Specifically, "Run-time error '13': Type mismatch". When I
debug, it does highlight the row that you and EricG mentioned to
fix-

If .Cells(R, 1).Value = "DELETE" Then

I did correct my formula to reverse the .Cells(1, R)... to .Cells(R,
1).....

Any suggestions?

Thanks again

 | 
Pages: 1
Prev: Sorting help !!!!!!!!!!!!!!!!!
Next: Get a list of open workbooks and choose one