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

[cardan]

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

 

[Wouter HM]

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

 

[EricG]

This:

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

Should probably be this:

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

HTH,

Eric

 

[cardan]

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

 

[JLGWhiz]

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.

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

 

[cardan]

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.







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 think I have it working now. I am not sure of the exact problem but
I changed the Ctrl+Shft+F shortcut to Ctrl+Shft+M. I think the Ctrl
+Shift+F shortcut was being used someplace else, however I am not sure
where since I don't have any other macros in it. Thank you for your
help!

 

[cardan]

I think I have it working now.  I am not sure of the exact problem but
I changed the Ctrl+Shft+F shortcut to Ctrl+Shft+M. I think the Ctrl
+Shift+F shortcut was being used someplace else, however I am not sure
where since I don't have any other macros in it.  Thank you for your
help!

I may have spoken too soon. I tried the test in a new workbook and it
worked fine. However, when I put it into my workbook, I am still
getting the same run-time, mismatch error and it is still
acknowledging something is wrong with the " If .Cells(R, 1).Value =
"DELETE" Then" line. I have tried retyping it, copying and pasting,
removing the whole macro and pasting a copied version back in.

Could it be my setup? I usually "record" a macro and immediately stop
recording and the cut and paste it into. The macros are being pasted
in seperate Modules (Module 1, Module 2, etc...). Does this have
anything to do with it?

In my workbook, Column A is a set of formulas that link back to
another sheet that returns either the word "DELETE" or another word
(such as Midwest, Northwest). The data set is ~7400 rows long. Does
this matter?

 

[JLGWhiz]

In theory, it should not matter that you have formulas in the cells, nor
what links the formulas have, because you are testing the cell value with
the If statement. The If statement should be only testing for true or
false. That is why I do not understand where the type mismatch is coming
from. It should just return false and go on to the next iteration. So, to
find out what is happening, you need to step through the procedure one line
at a time and check the value of your variables as you go. It is clear that
something is not what you expect it to be, or you would not be getting the
error message. Since, I do not get the error, I cannot come up with any
other answer.

I think I have it working now. I am not sure of the exact problem but
I changed the Ctrl+Shft+F shortcut to Ctrl+Shft+M. I think the Ctrl
+Shift+F shortcut was being used someplace else, however I am not sure
where since I don't have any other macros in it. Thank you for your
help!

I may have spoken too soon. I tried the test in a new workbook and it
worked fine. However, when I put it into my workbook, I am still
getting the same run-time, mismatch error and it is still
acknowledging something is wrong with the " If .Cells(R, 1).Value =
"DELETE" Then" line. I have tried retyping it, copying and pasting,
removing the whole macro and pasting a copied version back in.

Could it be my setup? I usually "record" a macro and immediately stop
recording and the cut and paste it into. The macros are being pasted
in seperate Modules (Module 1, Module 2, etc...). Does this have
anything to do with it?

In my workbook, Column A is a set of formulas that link back to
another sheet that returns either the word "DELETE" or another word
(such as Midwest, Northwest). The data set is ~7400 rows long. Does
this matter?