Consulting

Results 1 to 3 of 3

Thread: How to clear all blank in the cell for the whole worksheet

  1. #1

    How to clear all blank in the cell for the whole worksheet

    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?

    Last edited by Aussiebear; 04-27-2023 at 01:00 AM. Reason: Adjusted the code tags

  2. #2

  3. #3
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •