I record a macro in Excel, but then the code throws an error?

Question

I'm using this code

Sub Cleanse()
    '
    ' Cleanse Macro
    '
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
End Sub

I had Excel autogenerate it by:

Pressing Record Macro
Going into special
Selecting all blank cells
Using delete selection on the cells
Stop recording Macro

Yet when I undo and run the macro to try and let it do the same thing it says:

I assume you selected at least one cell more than once! At least that is what your error message says. — Pᴇʜ, Mar 03 '17 at 13:27
I only use select once though Peh? Tretom, I want to take a sheet and delete all rows that have an empty cell. — Kelly, Mar 03 '17 at 13:30

score 0 · Answer 1 · edited May 23 '17 at 12:09

Your error is because your selection (the one you made manually before running the sub) includes the same cell twice, at least once. Edit: this could be due to multiple cells on the same row being empty, then you're selecting the entire row of each of those cells! See code edit below for a fix.

You should try to avoid using Select, despite the Macro Recorder using it a lot. See here: How to avoid using Select in Excel VBA macros

So a better format for your sub would be this:

Sub Cleanse()        
    ' Cleanse Macro for deleting rows where cells in a range are blank
    '
    Dim myRange as Range
    ' Set the selection range to the first column in the used range.
    ' You can use this line to select any range you like.
    ' For instance if set on manual selection, you could use
    ' Set myRange = Selection. But this won't solve your actual problem.
    Set myRange = ActiveSheet.UsedRange.Columns(1)
    ' Delete all rows where a cell in myRange was blank
    myRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Edit: Can cycle over all columns like below, avoids overlapping ranges from EntireRow.

Sub Cleanse()        
    ' Cleanse Macro for deleting rows where cells in a range are blank
    '
    Dim myRange as Range
    Dim colNum as Long
    ' Cycle over all used columns
    With ActiveSheet
        For colNum = 1 To .UsedRange.Columns.Count + .UsedRange.Columns(1).Column
        ' Set the selection range to the column in used range.        
            Set myRange = .UsedRange.Columns(colNum)
            ' Delete all rows where a cell in myRange was blank
            myRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        Next colNum
    End With
End Sub

Nah I think I'm an idiot, forgot to replace the comments with required code. Although I'm not entirely sure how to set it to select all columns — Kelly, Mar 03 '17 at 14:02
Tried running it in a new work sheet with four columns, all with missing data, for some reason only cleansed those missing data in the first two columns — Kelly, Mar 03 '17 at 14:07
@Kelly If you want it for all columns just use `ActiveSheet.UsedRange` in place of `ActiveSheet.UsedRange.Columns(1)` — Wolfie, Mar 03 '17 at 14:39

score 0 · Accepted Answer · answered Mar 03 '17 at 13:51

0

you could use this sub

Sub CleanSheet(sht As Worksheet)
    On Error Resume Next
    Intersect(sht.UsedRange, sht.UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow).EntireRow.Delete
End Sub

to be called by your "Main" Sub as follows

Sub Main()
    CleanSheet Worksheets("mySheetToBeCleanedName") '<--| change "mySheetToBeCleanedName" to actual sheet name you want to clear
End Sub

answered Mar 03 '17 at 13:51

user3598756

28,893
4
18
28

@Kelly, did you try this solution? – user3598756 Mar 03 '17 at 14:26
Hi this works on my simple version (15 rows, 5 columns) but on my actual data is just deletes the entire set. Any idea why? (When I run the delete blanks manually it only deletes a portion not all) – Kelly Mar 03 '17 at 14:29
this code is to _"delete all rows that have an empty cell."_ as you asked for in your comment. is this not true anymore? – user3598756 Mar 03 '17 at 14:47
The issue I'm having it does do that, which is what I want, but on my test data it deletes EVERY row, blank cell or not. Weirdly on my self made data (a simple set of 15 rows with junk data I typed in) however it works perfectly well. – Kelly Mar 03 '17 at 14:51
It must be because in your test you have some not-empty cell somewhere outside your "data". Try finding it: you may open Immediate Window (CTRL+G), type `?ActiveSheet.UsedRange` and press return to see the outcome – user3598756 Mar 03 '17 at 14:55
I couldn't get CTRL+G to work, it brought up the go to menu for me, but I tried selecting the little arrow in the top right (select all? I think?) And used the select blank and delete entire row and it worked fine. Not sure what's up with the code, or the used range. – Kelly Mar 03 '17 at 15:04
I forgot to mention: you have to be in VBA IDE (ALT+f11) to press CTRL+G and pop Immediate Window out – user3598756 Mar 03 '17 at 15:11
Ah ok, by the way could the fact it's working in my Macro Enabled work book but deletes all in my test data which is .csv be the issue? (edit: Nope, saved it as macro, still same issue) – Kelly Mar 03 '17 at 15:16
Type mismatch error coming up when I try and run immediate with ?ActiveSheet.UsedRange in it – Kelly Mar 03 '17 at 15:21
`?ActiveSheet.UsedRange.Address` – user3598756 Mar 03 '17 at 15:25
It returns the correct cell address range, not sure why it's bugging and deleting all – Kelly Mar 03 '17 at 15:27
There was a bloody empty column! Thank you! – Kelly Mar 06 '17 at 16:12

I record a macro in Excel, but then the code throws an error?

2 Answers2