• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to clear all cells in a range, even those filtered out

H

Hartke

New Member
Hello, my knowledge of VBA is very elementary, a fact I intend to change with one of your upcoming classes. Unfortunately I cannot wait until January to solve this problem!


I have a set of user-driven spreadsheets that are manually updated with data on a monthly basis from our department. I would like to clear all data in a specific range prior to its being updated.


I attempted to use include this command in my updating VBA:


Sheets("A Sheet").Select

Range("F1:G65000").Clear


It works fine, but does not clear any cells filtered out, which happens all the time because these are working documents that users regularly filter, sort etc.


Can anyone suggest some VBA that clears all cells within a range regardless of whether or not it is hidden or filtered out?
 
This should help

[pre]
Code: 
Sub ClearRange()
ActiveSheet.AutoFilterMode = False
Range("F1:G65000").EntireRow.Hidden = False
Range("F1:G65000").EntireColumn.Hidden = False
Range("F1:G65000").ClearContents 'This deletes only cell contents
'Range("F1:G65000").Clear 'This deletes cell contents and formatting
End Sub
BTW, if you're changing the clearing the same range in every sheet, you can do this:

Sub ClearRangeInAllSheets()
Dim MyRange As String
MyRange = "F1:G65000"
For Each ws In ThisWorkbook.Worksheets
With ws
.AutoFilterMode = False
.Range(MyRange).EntireRow.Hidden = False
.Range(MyRange).EntireColumn.Hidden = False
.Range(MyRange).ClearContents 'This deletes only cell contents
'.Range(MyRange).Clear 'This deletes cell contents and formatting
End With
Next ws
End Sub
[/pre]
 
Hi prasaddn,

I would think so, but I haven't been able to find a way yet. =(

If you find a way, let us know!
 
Thank you Luke, one more n00bish question -


Is there a way to define the sheet in question rather than the active sheet? I'd like to be able to update from any tab without the possibility of removing data by mistake.
 
Sure, tweaking the second macro a little, you could do something like this:

[pre]
Code: 
Sub ClearRangeInAllSheets()
Dim MyRange As String
MyRange = "F1:G65000"
'Define which worksheet you want to work on
With Worksheets("My Sheet")
.AutoFilterMode = False
.Range(MyRange).EntireRow.Hidden = False
.Range(MyRange).EntireColumn.Hidden = False
.Range(MyRange).ClearContents 'This deletes only cell contents
'.Range(MyRange).Clear 'This deletes cell contents and formatting
End With

End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top