Solved: Clear cells where using a certain font

[sassora]

How do I clear cells in a worksheet that use a particular font, i.e. remove what is in them?

[MikeO]

Dim Rng As Range

For Each Rng In ActiveSheet.UsedRange.Cells
If Rng.Font.Name = "Ariel" Then 'Put whatever font you want to clear here
Rng.Clear
End If
Next Rng

[malik641]

That could get a little slow on large worksheets. Check this out:

Option Explicit

Public Sub DeleteCells(ByRef sFont As String)
Application.ScreenUpdating = False

Dim Rng As Excel.Range
Dim firstAddress As String, sDefaultFont As String

sDefaultFont = Application.FindFormat.Font.Name

Application.FindFormat.Font.Name = sFont
Set Rng = ActiveSheet.Cells.Find(What:="*", SearchFormat:=True)

If Not Rng Is Nothing Then
firstAddress = Rng.Address
Do
Rng.ClearContents
Set Rng = ActiveSheet.Cells.Find(What:="*", After:=Rng, SearchFormat:=True)
If (Rng Is Nothing) Then Exit Do
Loop While (Rng.Address <> firstAddress)
End If

Application.FindFormat.Font.Name = sDefaultFont
Application.ScreenUpdating = True
End Sub


Public Sub test()
Call DeleteCells("Arial")
End Sub

Just run "test".

[sassora]

Thanks as I had a 'small' amount of data

Dim Rng As Range

For Each Rng In ActiveSheet.UsedRange.Cells
If Rng.Font.Name = "Impact" Then 'Put whatever font you want to clear here
Rng.ClearContents
End If
Next Rng

did the trick. I edited it so that it only removed the content and not the formatting too.

[malik641]

That's cool. But please take a look at the method I used for future reference. With that method, you could check all sorts of interesting things like finding cells that have a Yellow fill in it or which cells have italic font and more. Pretty powerful stuff :)

[MikeO]

That could get a little slow on large worksheets. Check this out:

Option Explicit

Public Sub DeleteCells(ByRef sFont As String)
Application.ScreenUpdating = False

Dim Rng As Excel.Range
Dim firstAddress As String, sDefaultFont As String

sDefaultFont = Application.FindFormat.Font.Name

Application.FindFormat.Font.Name = sFont
Set Rng = ActiveSheet.Cells.Find(What:="*", SearchFormat:=True)

If Not Rng Is Nothing Then
firstAddress = Rng.Address
Do
Rng.ClearContents
Set Rng = ActiveSheet.Cells.Find(What:="*", After:=Rng, SearchFormat:=True)
If (Rng Is Nothing) Then Exit Do
Loop While (Rng.Address <> firstAddress)
End If

Application.FindFormat.Font.Name = sDefaultFont
Application.ScreenUpdating = True
End Sub


Public Sub test()
Call DeleteCells("Arial")
End Sub

Just run "test".


Very Nice! I've added that to my arsenal of knowledge.

[malik641]

Thanks :)
It's all in the "Application.FindFormat". It's awesome, you can even look for "Wrap Text" cells and even merged cells. This thread helped me to find this method, so thanks for the question sassora!!

[lucas]

Hi Joseph,
It's excellent but a little daunting for inexperienced coders. A few comments would probably help.......

How are things down south?

[sassora]

That sounds rather useful. When I run it I get a run time error (invalid use of null).

The function reminded me of a time when I had a spreadsheet with thousands of rows and some clever clogs decided to mark out the 10 relevant lines by using red text. It was like finding a needle in a haystack so I found code to find the red text.

I'm sure this function will come in handy at some point or another.

[mdmackillop]

That sounds rather useful. When I run it I get a run time error (invalid use of null).
Try sDefaultFont = Application.StandardFont

[malik641]

Thanks Malcolm. I added that code right before I posted it, so it was untested.

Steve,
I have fixed up the code with comments (and the bug that Malcolm fixed) so that novice VBA'ers can benefit from this more easily.

And Florida's treating me just fine. I'm not taking any summer courses, so I picked up a programming job for the summer :) They want me to do programming with C# (another syntax to tackle!) and probably some VB.NET and SQL. I'm VERY excited about it! I need more work-related programming experience :thumb

Anyway, here's the code:
Public Sub DeleteSpecificFontCells(ByRef sFont As String)
Application.ScreenUpdating = False

Dim Rng As Excel.Range
Dim firstAddress As String, sDefaultFont As String

' Collect the font of the findformat (if any)
' if there is none, then store the standard font
sDefaultFont = IIf(IsNull(Application.FindFormat.Font.Name), _
Application.StandardFont, _
Application.FindFormat.Font.Name)

' Set the findformat font for searching cells for specific font
' This is what is used when "SearchFormat:=True"
Application.FindFormat.Font.Name = sFont

' Find the first cell with the findformat font
Set Rng = ActiveSheet.Cells.Find(What:="*", SearchFormat:=True)

' If a range was found from searching
If Not Rng Is Nothing Then
' loop control (if you keep using .Find(), you will wrap around to the
' beginning again)
firstAddress = Rng.Address
' Loop through ranges with specific font, deleting the contents of the cells
' This will leave any formating, though. Like fill, font stye, etc.
Do
Rng.ClearContents
' You MUST use this method to keep searching for specific FindFormat values
' Using .FindNext/.FindPrevious will NOT work
Set Rng = ActiveSheet.Cells.Find(What:="*", After:=Rng, SearchFormat:=True)
' Check if Rng was not found
If (Rng Is Nothing) Then Exit Do
Loop While (Rng.Address <> firstAddress)
End If

' Reset settings
Application.FindFormat.Font.Name = sDefaultFont
Application.ScreenUpdating = True
End Sub

Note: I changed the name of the program.

[tstav]

Very interesting Joseph,
hadn't been able to figure out the use of the SearchFormat parameter until your post.
ByRef (?) sFont as String

[malik641]

Very interesting Joseph,
hadn't been able to figure out the use of the SearchFormat parameter until your post.
ByRef (?) sFont as String
Yeah, I was going nuts looking for that answer myself. Then I found a forum where somebody used "Application.FindFormat.Font.Size = 8" and that gave me a clue in the right direction. I don't understand why this wasn't explained in the help file under Range.Find...

And I use ByRef for strings because it passes the arguments "By Reference" meaning that a call to the copy constructor is not made (which would eat up time for larger strings). For data types like Integer, Byte, and Long I would use ByVal because the cost of passing those data types is not much (look at their storage size to see what I mean...strings have a larger storage size and take time to make a copy). Also, arrays should ALWAYS be passed ByRef because of this.

[tstav]

Thanks for the recap on 'ByVal' and 'ByRef' Joseph. That's not what I meant, though. :)
I just wouldn't consider as 'heavy' the memory toll for passing ByVal strings like "Arial" or "Courier New" to the function. The byte-load they are carrying is less than a variant's (16 b).
As far as the Arrays are concerned, you just can't pass them ByVal at all. VBA just won't allow it.

[malik641]

Thanks for the recap on 'ByVal' and 'ByRef' Joseph. That's not what I meant, though. :)
I just wouldn't consider as 'heavy' the memory toll for passing ByVal strings like "Arial" or "Courier New" to the function. The byte-load they are carrying is less than a variant's (16 b). Well, there are 2 string types. Fixed-Length and Variable-Length. The Fixed-Length says the storage size depends only on the length of the string. And the Variable-Length states that the storage size is 10 bytes + string length. Now I'm pretty sure that means to say "characters that fill UP TO 10 bytes, then + additional data".

If I remember correctly each character is 2 bytes of storage. Most users don't touch fixed-length strings, so most of the time it's at least 10 bytes of storage. And think about "Times New Roman" which is 15 characters so that's 10 bytes (first 5 characters) + 20 bytes (last 10 characters) = 30 bytes....ouch. Even a fixed-length string would still be the same storage space for that.

So 30 bytes versus 1 byte for Byte data type versus 2 bytes for Boolean and Integer versus 4 bytes for Long and Single. That's enough difference for me to use ByRef, in my honest opinion.

If I am mistaken on this please let me know.


As far as the Arrays are concerned, you just can't pass them ByVal at all. VBA just won't allow it. I was wondering if they didn't allow them or not. I tried briefly checking, but couldn't find anything quick enough. Thanks for reminding me :)

[tstav]

Ok, I paid Wrox, O'Reilly, Wiley and Sybex books a short visit to brush up what I knew (or thought I knew) and here's what I came up with.

Fixed-length strings (like you said) are rarely used. From VB 2005 and onwards the type is rendered more or less obsolete due to 2 new classes that have been created (Sybex).

Now, as for the variable-length strings, O'Reilly states that the memory storage allocated is 4bytes + stringth length. All the others agree on 10 bytes + stringth length. None mentions that characters want 2 bytes each.

Anyways...
Maybe I'm just more hesitant than you in using ByRef since it allows the permanent change of the value of the passed parameter.

One way or another, it's been nice talking to you Joseph and again thank you for solving the everlasting mystery of the 'SearchFormat' parameter.

Best regards
tstav

[malik641]

Hey tstav,

I'm going to post back later tonight (I'm crazy busy at my job right now) in response to your post. If you want to discuss this with more detail, we can start another thread (Strings - Data storage and passing to arguments) to continue.

Let me know. I'm interested in topics like these because they can help us code faster/better. :)

-Joseph

[tstav]

I don't think I have much more to comment on this Joseph.
Taking into account the vast amounts of memory today's computers have and the power of contemporary chips, I would only add that in my personal opinion I wouldn't be all that cautious over one or two or even three dozens of extra bytes.
Make it a string array of hundreds of items and then I would pay more attention.
I would not disagree though with you that the more we know about this stuff, the more we know what we're doing.

Best regards,
tstav

[malik641]

Make it a string array of hundreds of items and then I would pay more attention.
I would not disagree though with you that the more we know about this stuff, the more we know what we're doing.
And that's basically my point. I understand that this particular application for the OP doesn't really need a super-efficient function for what they are doing. I just think it's interesting to know how much time you can save with some extra know-how. Plus it can give you a better understanding of how VBA works internally. I have made tons of slow code before I started learning about this info. And it has helped me dramatically and has given me new ideas along the way. That's why I think it's important to know them.

Check out this link if you're interested:
http://www.aivosto.com/vbtips/stringopt.html

Anyway it was good talking with you tstav.

[tstav]

I second everything you said.

Thanks for the link, I'll check it out

:thumb