PDA

View Full Version : How to clear all blank in the cell for the whole worksheet



climbp19981
10-01-2019, 05:02 AM
Sub deleteblank Dim book_sht As Worksheet
Dim sht_Rng As Range
Dim i, data_rows
Set book_sht = Worksheets(1)
Set sht_Rng = book_sht.UsedRange
Range("B2").Select
data_rows = sht_Rng.Rows(sht_Rng.Rows.Count).Row
For i = 1 To data_rows
ActiveCell.Value = Application.WorksheetFunction.Substitute(Trim(ActiveCell.Value), " ", "")
ActiveCell.Offset(1, 0).Select
Next
Set sht_Rng = Nothing
Set book_sht = Nothing
End Sub




I have the above VBA code that helps to clear one column of cell.
How can I modify it so that the code can help me to look through all column?

:bow:

climbp19981
11-03-2019, 08:44 AM
:crying::crying::crying:

Logit
11-03-2019, 09:34 AM
.


Option Explicit


Sub CleanSht()
Dim rCell As Range
Dim rText As Range


Set rText = Cells.SpecialCells( _
xlCellTypeConstants, _
xlTextValues)

For Each rCell In rText
If Trim(rCell.Value) = "" Then
rCell.ClearContents
End If
Next

Set rText = Nothing
Set rCell = Nothing
End Sub