Adding spaces automatically

[wmtsub]

So My sheet looks bad as cells text spill over into unused cells. I see if I add a "space" that will stop that from happening. Can anyone suggest a way to be able to search thru the sheet and if the cell is blank auto populate it with a "Space"?

Or even better a conditional formatting so it is automatic?

 

[Fluff]

Try

Sub addspace()
ActiveSheet.UsedRange.SpecialCells(xlBlanks).Value = " "
End Sub

 

[kvsrinivasamurthy]

For the range A1:M9 (can be changed) . This is the code for macro.

Sub Macro1()

Range("A1:M9").SpecialCells(xlCellTypeBlanks).Value = " "


End Sub

 

[wmtsub]

Thank you both

 

[Fluff]

You're welcome

 

[Rick Rothstein]

For the range A1:M9 (can be changed) . This is the code for macro.

Sub Macro1()

  Range("A1:M9").SpecialCells(xlCellTypeBlanks).Value = " "

End Sub

If the OP's data should occupy a lot more cells and if there are a considerable number of blank cells, perhaps it would be better to only fill the blank cells immediately to the right of a filled cell with a space character instead of putting the space character into every blank cell in the range. If the OP would like to pursue this approach, all he has to do is replace your single line of code with this one...

[table="width: 500"]
[tr]
	[td]Range("A1:M9").SpecialCells(xlConstants).Offset(, 1).SpecialCells(xlBlanks).Value = " "[/td]
[/tr]
[/table]

Also, I would note that if the OP's data spans to Column M, then the address in the Range object call should be changed to A1:N9 so that the filled cells in Column M (the last data column in the range) do not spill out into the adjacent unfilled cells (assuming that is what the OP wants).

 

[Peter_SSs]

Also, I would note that if the OP's data spans to Column M, then the address in the Range object call should be changed to A1:N9 so that the filled cells in Column M (the last data column in the range) do not spill out into the adjacent unfilled cells (assuming that is what the OP wants).

But if the OP's UsedRange only extends to column M, then the empty cells in column N will not form part of the SpecialCells(xlBlanks) range and therefore still not get filled by that line of code.

 

[Rick Rothstein]

But if the OP's UsedRange only extends to column M, then the empty cells in column N will not form part of the SpecialCells(xlBlanks) range and therefore still not get filled by that line of code.

Hmm! Good point! I guess two lines of code would be needed with my method then...

[table="width: 500"]
[tr]
	[td]Range("A1:M9").SpecialCells(xlConstants).Offset(, 1).SpecialCells(xlBlanks).Value = " "
Columns("M").SpecialCells(xlConstants).Offset(, 1).Value = " "[/td]
[/tr]
[/table]