Toggle Button To Fill In Blank Cells With N/A

[Storm08]

Hello,

I have the attached spreadsheet where I need to fill in any blank cells with "N/A".
The range is quite large, so with the current code, it takes a long time to run.

Is there any way to make this more efficient?:

Please Login or Register  to view this content.

Many Thanks!
Storm08

[Marc L]

According to your attachment use Excel / VBA basics at once rather than such slow looping cell by cell :

Private Sub Toggle_NA_Click() 
    With ActiveSheet.UsedRange.Rows("3:" & ActiveSheet.UsedRange.Rows.Count) 
        If Toggle_NA.Value Then On Error Resume Next: .SpecialCells(4) = "N/A" Else .Replace "N/A", Empty 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Storm08]

According to your attachment use Excel / VBA basics at once rather than such slow looping cell by cell :

Private Sub Toggle_NA_Click() 
    With ActiveSheet.UsedRange.Rows("3:" & ActiveSheet.UsedRange.Rows.Count) 
        If Toggle_NA.Value Then On Error Resume Next: .SpecialCells(4) = "N/A" Else .Replace "N/A", Empty 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Wow. That code is great! Thank you.

One more question.

If I wanted to change the range where this code runs, how would I do that?
For instance, if I wanted to run the code in Range("A2:B110"), how would I change that code?

[Marc L]

In the With Codeline …

[Storm08]

Sorry, that was a very obvious question! I just figured it out too

Thank you very much Marc!

+Rep!