VBA to regnize empty cells that arent empty.

[CreativeUsername]

I have a block of data that comes from CSV and is saved as XLSX. The cells below this block look blank but fail an "is blank" test. I don't know what this condition would be called to google how to identify them. What I need is a VBA string that goes to the end of a block of numbers but stops there... then clears the contents of ALL cells in ALL columns below that.

Then my code that copies the block of data below itself several times will run fine.

Any help is appreciated.

 

[JoeMo]

Don't know w/o seeing your work sheet if this macro from David McRitchie will help or not, but you can try it. Select all the "empty" cells that aren't empty and then run the code.

Sub TrimALLMcRitchie()
   'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm
   '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
   ' - Optionally reenable improperly terminated Change Event macros
      Application.DisplayAlerts = True
      Application.EnableEvents = True   'should be part of Change Event macro
   If Application.Calculation = xlCalculationManual Then
      MsgBox "Calculation was OFF will be turned ON upon completion"
   End If
   
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.Replace What:=Chr(160), replacement:=Chr(32), _
     lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(13), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(21), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   '---------------------------
   Selection.Replace What:=Chr(8), replacement:=Chr(32), _
      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(9), replacement:=Chr(32), _
      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next
   For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

 

[CreativeUsername]

I tried clear contents and deleting the "empty cells" but it still selects a much larger range than I need and then finds the first cell at the end of a large range of "blank" cells which is not what I want.

 

[CreativeUsername]

That seems to do a ton of stuff unconnected to my task. ? ? ?

 

[JoeMo]

I tried clear contents and deleting the "empty cells" but it still selects a much larger range than I need and then finds the first cell at the end of a large range of "blank" cells which is not what I want.

What is "it"? Did you try selecting cells below your block and then running the macro?