0

I want to clear the whole cell including formulas from the last blank cell. Here is what i'm trying to work with but its not working. Would other code affect it? Thanks

Dim myLastRow As Long
Dim clearCell As Long

    Application.ScreenUpdating = False

'   Find last row
    myLastRow = Cells(Rows.Count, "C").End(xlUp).Row

'   Loop through range
    For clearCell = 4 To myLastRow
        If Cells(clearCell, "C").Value = "" Then Range(Cells(clearCell, "C"), Cells(clearCell, "C")).Clear
    Next clearCell

    Application.ScreenUpdating = True
Community
  • 1
  • 1
Mils
  • 45
  • 1
  • 4
  • Do you want to clear everything? Try changing to `Cells(clearCell, "C").Value <> ""` You are making If equal Blank and you need If different Blank – danieltakeshi Aug 17 '17 at 00:43
  • The cell in blank to look at but the formula is in there and i want to remove the formula when it reaches the last blank cell – Mils Aug 17 '17 at 00:47
  • Only the **last** blank cell? Or all the cells that are blank? Or only the last cell, if it is blank? And what do you mean by the statement "but its not working". – YowE3K Aug 17 '17 at 00:48
  • By the statement it's not working, i mean that it was working when I was using F8 to step through the code bit by bit but when I run the whole thing it didn't work. – Mils Aug 17 '17 at 00:50
  • all the blank cells please – Mils Aug 17 '17 at 00:51
  • Your code is set up to work on the "active" sheet, whichever sheet that might be. Do you have other code that is changing the active sheet, and therefore your posted code is (potentially) clearing out cells on a sheet that you aren't expecting it to be operating on? – YowE3K Aug 17 '17 at 00:52
  • I have just changed to formula in the cells i want to clear out to this =IF(AF37="0","-",AF37) so it leaves a - in there If i change the "" to "-" and the sheet name i want to clear out is called Stow, would you be able to help me redo the code? i'm struggling with it. – Mils Aug 17 '17 at 00:59

1 Answers1

1

I don't see anything that would cause an error in your code, other than the fact that your references aren't qualified with the sheet that is being used - which means everything will default to operating on ActiveSheet which may not be the sheet you are wanting it to work on.

Assuming the sheet you want to process has a Name of "Stow", the following code should be safer:

Dim myLastRow As Long
Dim clearCell As Long

Application.ScreenUpdating = False

'Use a "With" block to save typing Worksheets("Stow") in lots of places
'(e.g. within the block we can type ".Cells" instead of "Worksheets("Stow").Cells" and
' ".Rows" instead of "Worksheets("Stow").Rows")
With Worksheets("Stow")
    '   Find last row
    myLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

    '   Loop through range
    For clearCell = 4 To myLastRow
        ''Clear any cells with a value of ""
        'If .Cells(clearCell, "C").Value = "" Then .Cells(clearCell, "C").Clear

        'Clear any cells with a value of "-"
        If .Cells(clearCell, "C").Value = "-" Then .Cells(clearCell, "C").Clear
    Next clearCell
End With
Application.ScreenUpdating = True
YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • Thats great thanks alot mate. I just been playing with the code and took into consideration what you said and before the code i used this to help me Worksheets("Stow").Activate i'm a bit of a novice if you hadn't had guessed – Mils Aug 17 '17 at 01:22
  • @Mils `Worksheets("Stow").Activate` would have made "Stow" the active sheet, and then your code would have probably worked as it was. But using `Activate` and `Select` is a bad habit to get into, because it leads to unexpected things happening (when you haven't activated the sheet/cell that you think you are using) and also slows your code down. [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/6535336) should be required reading for anyone who has any intention of writing VBA code. – YowE3K Aug 17 '17 at 01:26