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?


  2. #2

  3. #3
    VBAX Mentor Logit's Avatar
    Joined
    Sep 2016
    Posts
    439
    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
  •