macro/vba fix for not a blank, blank in Go To Special

[LorettaAlsop]

I am writing vba code (thru using macros) to format a ws. In a portion of the formatting I identify the blank cells and replace them with the cell data from the above cell using the following successfully (I found this online and it works as intended and perfect):

Sub fillme()
With Range("A:A")
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
End Sub

However, I need to do more & am stuck... I have 2 additional columns that I want to do this exact fill --- But the two additional col that appear to have blanks are not really blank.... there must be something imbedded -- I can complete delete on the ghost char(s) manually and move on -- but I need to do this in a macro/vba code so that my colleagues can execute the formatting of a spreadsheet effortlessly.

Any ideas?

 

[shknbk2]

Hello and welcome to the forum.

If you can clear the contents of the ghost cells, you can then run the code you have on those columns. Let's figure out what the code sees in those cells.

Select one of the ghost cells in the spreadsheet.
Open the Visual Basic Editor (where you have the above vba code)
Go to the Immediate Window (usually on the lower part of the window if it is visible; if not, hit Ctrl + G)
In the Immediate window, type each of the following lines and report what output results (include the question marks - they tell the window to output the result):
?ActiveCell.Value
?Len(Activecell.Value)
?Asc(Left(Activecell.Value, 1))

Since the cells appear blank on the spreadsheet, nothing might also show with the first line above, but the other 2 should show some result.

Let's use this as a start to figure out what is in the cell. Once we know what is there, we can figure out how to clear the values.

 

[shknbk2]

By the way, I forgot to say that after putting in each line in the Immediate window, hit Enter.

 

[LorettaAlsop]

By the way, I forgot to say that after putting in each line in the Immediate window, hit Enter.

the ghost cell was row 212.... looks empty. when I ysed your active cell lines the output was nothing, 69, 32

 

[LorettaAlsop]

the ghost cell was row 212.... looks empty. when I ysed your active cell lines the output was nothing, 69, 32

View attachment 40434

 

[LorettaAlsop]

ty for responding and helping!

 

[shknbk2]

I wonder if it is filled with 69 spaces. What is the output for:
?ubound(split(activecell.Value," "))

 

[LorettaAlsop]

I wonder if it is filled with 69 spaces. What is the output for:
?ubound(split(activecell.Value," "))

69

Sounds plausible. The initial spreadsheet came from a data dump, first step I perform in my macro is text to column with delimiter ^.

 

[LorettaAlsop]

the view below is the data dump... the row 212 we've discussed came from row 258 ...

 

[shknbk2]

Ok. We can use this code on the proper columns to remove the spaces. It should turn the ghost cells into blank cells that would work with your code.

Range("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart

I don't know if you want to run this on column A as well as the other 2 columns, but why not? Add this before the .SpecialCells line. Since it is after the With line, the code above doesn't need the "Range" part:

.Replace What:=" ", Replacement:="", LookAt:=xlPart

Then, you can copy and paste 2 other versions, changing the columns.