VBA Code: Rest Last Cell & Fill All Blanks With -

[Nanogirl21]

[FONT=&quot]Is there a VBA code that I can add to the end of an existing macro that will reset the last cell on worksheet and fill all blanks with a dash (-)? I'd like to do this with every sheet in the workbook. [/FONT]

 

[jproffer]

You want dashes in ALL the cells...as in all 63,000,000,000 cells in the worksheet?

If so, I think there's a setting to do that without adding anything to your code, but I'll have to wait til tomorrow to look around and find it.

 

[Nanogirl21]

You want dashes in ALL the cells...as in all 63,000,000,000 cells in the worksheet?

If so, I think there's a setting to do that without adding anything to your code, but I'll have to wait til tomorrow to look around and find it.

No, only cells within the active range of data. The range would be identified when the last cell is rest. When the last cell is reset all unused rows and columns will be deleted.

I know you can go to special and select all blanks. Then you can enter - (or whatever you want to fill blanks with) and hit CTRL + ENTER to fill all cells with the value. However, i'm looking for this in a VBA format to add to the end of existing code.

 

[jproffer]

This is going to be an "off-the-cuff" guess until I get to work (where I have excel to test on) tomorrow, but maybe it'll be close. What you can do manually with specialcells, can be done in VBA as well.

Maybe something like this:

UsedRange.SpecialCells(xlBlankCells).Value = "-"

Again...I'm not terribly familiar with using SpecialCells in VBA, but I know it's possible. Intellisense should show your options after you open the parenthesis after SpecialCells (I hope). If not, and if someone else doesn't come along with an answer, I'll try a couple things out tomorrow and get back to you.

 

[Nanogirl21]

This is going to be an "off-the-cuff" guess until I get to work (where I have excel to test on) tomorrow, but maybe it'll be close. What you can do manually with specialcells, can be done in VBA as well.

Maybe something like this:

UsedRange.SpecialCells(xlBlankCells).Value = "-"

Again...I'm not terribly familiar with using SpecialCells in VBA, but I know it's possible. Intellisense should show your options after you open the parenthesis after SpecialCells (I hope). If not, and if someone else doesn't come along with an answer, I'll try a couple things out tomorrow and get back to you.

Thank you. I was able to figure it out. I used the below code. Hopefully it can help someone else too!

    Cells.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "-"

 

[Fluff]

FYI you can do that without selecting anything like

Cells.SpecialCells(xlBlanks).Value = "-"

 

[Nanogirl21]

FYI you can do that without selecting anything like

Cells.SpecialCells(xlBlanks).Value = "-"

Thank you. Is there a way to apply to all sheets in workbook?

If no blanks found on sheet move to the next sheet.

 

[Fluff]

How about

Sub FillBlanks()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      On Error Resume Next
      Ws.Cells.SpecialCells(xlBlanks).Value = "-"
      On Error GoTo 0
   Next Ws
End Sub

 

[Nanogirl21]

How about

Sub FillBlanks()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      On Error Resume Next
      Ws.Cells.SpecialCells(xlBlanks).Value = "-"
      On Error GoTo 0
   Next Ws
End Sub

Perfect

 

[Fluff]

Glad we could help & thanks for the feedback