Recently, I had a problem with too many columns in a data set and
asked for assistance with a Macro to delete columns based on the value
in Row 1. Chip Pearson responded with a fantastic macro that would
delete the column if the word “DELETE” was in the first row. JLGWhiz
also helped me understand some issues as well (Thank you Chip and
JLG!)

Even with the deletion of columns from my data set, it is still too
large so I need to have a similar macro that will delete the rows
based on the value in Column A. I have tried to modify the macro by
switching the column references to row references, but I keep getting
errors and I have sub par macro skills. I would like to keep the
macros similar since they work on selected sheets rather than just
active. Below is Chip’s original macro.

Sub DeleteColumns()
Dim WS As Worksheet
Dim R As Range
Dim DeleteThese As Range
Dim LastCol As Long
Dim C As Long
For Each WS In _
Application.ActiveWindow.SelectedSheets
Set DeleteThese = Nothing
With WS
LastCol = .Cells(1, .Columns.Count) _
.End(xlToLeft).Column
For C = LastCol To 1 Step -1
If .Cells(1, C).Value = "DELETE" Then
If DeleteThese Is Nothing Then
Set DeleteThese = .Columns(C)
Else
Set DeleteThese = _
Application.Union(DeleteThese, .Columns(C))
End If
End If
Next C
If Not DeleteThese Is Nothing Then
DeleteThese.Delete
End If
End With
Next WS
End Sub


Below is the macro as I have modified it. I am not sure where I am
going wrong. Any feedback would be most helpful!

Sub DeleteRows()
'
' DeleteRows Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Dim WS As Worksheet
Dim C As Range
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(1, .Rows.Count) _
.End(xlUp).Row
For R = LastRow To 1 Step -1
If .Cells(1, R).Value = "DELETE" Then
If DeleteThese Is Nothing Then
Set DeleteThese = .Rows(C)
Else
Set DeleteThese = _
Application.Union(DeleteThese, .Rows(C))
End If
End If
Next R
If Not DeleteThese Is Nothing Then
DeleteThese.Delete
End If
End With
Next WS
End Sub

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

On May 13, 12:53*pm, Wouter HM 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

I copied the code as revised by Wouter HM and it worked as intended, without
error. It seems odd that you would get a type mismatch error on an
If...Then statement, since it only evaluates true or false. However, if you
tried to type in corrections, it might be better if you just copy the good
code and replace the one that is malfunctioning.




"cardan" wrote in message
...
On May 13, 12:53 pm, Wouter HM 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

This:

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

Should probably be this:

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

HTH,

Eric

"cardan" wrote:

Recently, I had a problem with too many columns in a data set and
asked for assistance with a Macro to delete columns based on the value
in Row 1. Chip Pearson responded with a fantastic macro that would
delete the column if the word €œDELETE€� was in the first row. JLGWhiz
also helped me understand some issues as well (Thank you Chip and
JLG!)

Even with the deletion of columns from my data set, it is still too
large so I need to have a similar macro that will delete the rows
based on the value in Column A. I have tried to modify the macro by
switching the column references to row references, but I keep getting
errors and I have sub par macro skills. I would like to keep the
macros similar since they work on selected sheets rather than just
active. Below is Chips original macro.

Sub DeleteColumns()
Dim WS As Worksheet
Dim R As Range
Dim DeleteThese As Range
Dim LastCol As Long
Dim C As Long
For Each WS In _
Application.ActiveWindow.SelectedSheets
Set DeleteThese = Nothing
With WS
LastCol = .Cells(1, .Columns.Count) _
.End(xlToLeft).Column
For C = LastCol To 1 Step -1
If .Cells(1, C).Value = "DELETE" Then
If DeleteThese Is Nothing Then
Set DeleteThese = .Columns(C)
Else
Set DeleteThese = _
Application.Union(DeleteThese, .Columns(C))
End If
End If
Next C
If Not DeleteThese Is Nothing Then
DeleteThese.Delete
End If
End With
Next WS
End Sub


Below is the macro as I have modified it. I am not sure where I am
going wrong. Any feedback would be most helpful!

Sub DeleteRows()
'
' DeleteRows Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Dim WS As Worksheet
Dim C As Range
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(1, .Rows.Count) _
.End(xlUp).Row
For R = LastRow To 1 Step -1
If .Cells(1, R).Value = "DELETE" Then
If DeleteThese Is Nothing Then
Set DeleteThese = .Rows(C)
Else
Set DeleteThese = _
Application.Union(DeleteThese, .Rows(C))
End If
End If
Next R
If Not DeleteThese Is Nothing Then
DeleteThese.Delete
End If
End With
Next WS
End Sub


.