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

LorettaAlsop

New Member
Joined
Jun 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
385
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 5, 2016
Messages
385
Office Version
  1. 365
Platform
  1. Windows
By the way, I forgot to say that after putting in each line in the Immediate window, hit Enter.
 

LorettaAlsop

New Member
Joined
Jun 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

1623198364506.png
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
385
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

LorettaAlsop

New Member
Joined
Jun 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
the view below is the data dump... the row 212 we've discussed came from row 258 ...

1623199753372.png
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
385
Office Version
  1. 365
Platform
  1. Windows
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.
VBA 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:
VBA Code:
.Replace What:=" ", Replacement:="", LookAt:=xlPart

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

Forum statistics

Threads
1,136,303
Messages
5,674,967
Members
419,537
Latest member
ucatchy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top