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?
 

LorettaAlsop

New Member
Joined
Jun 8, 2021
Messages
14
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.
It does work! not blank blanks are fixed ---- you are awesome... THANK You!!!!

I noticed 2 things with my code (not your fix) --- maybe you have a fix for those as well? I need to end it properly... it should stop at row 488 of this particular ws but the copy is continuing for col B, C, D, T (but not for A).

1623201698871.png



Prob my not ending it properly and repetition instead of a single line contributes to it taking a fair amount of time. by any chance can you tell me how to write this as one line and speed it up....

1623201224483.png
 

Attachments

  • 1623201531230.png
    1623201531230.png
    31.6 KB · Views: 2

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
373
Office Version
  1. 365
Platform
  1. Windows
How about this?
VBA Code:
Sub fillme()
    Dim LastRow As Integer
    
    Range("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart
    Range("T:T").Replace What:=" ", Replacement:="", LookAt:=xlPart
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    With Range("A1:A" & LastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    With Range("B1:B" & LastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    With Range("C1:C" & LastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    With Range("D1:D" & LastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    With Range("T1:T" & LastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
End Sub
 
Solution

LorettaAlsop

New Member
Joined
Jun 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
As long as I only have 1 routine for a SINGLE column (T - or any one of the columns) in the sub it performs perfectly. The non-blanks become blank & the blank is populated with the above cell data. (I DO NOT get the extra rows TO the ws limit of 1048578 rows & it runs fast).

When I copy the routine to perform the same routine for multiple columns (A, B, C, D, T) then I DO get the extra rows TO the ws limit of 1048578 row & it runs fast).

Looking for tips on how to condense my code to eliminate the over population of data at the end when it should stop...

Code:

Sub fillme()
'Range("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart
'With Range("A:A")
' .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
' .Value = .Value
'End With
'With Range("B:B")
' .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
' .Value = .Value
'End With
'With Range("C:C")
' .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
' .Value = .Value
'End With'With Range("D:D")
' .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
' .Value = .Value
'End With
Range("T:T").Replace What:=" ", Replacement:="", LookAt:=xlPart
With Range("T:T")
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
End Sub
 

LorettaAlsop

New Member
Joined
Jun 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
How about this?
VBA Code:
Sub fillme()
    Dim LastRow As Integer
   
    Range("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart
    Range("T:T").Replace What:=" ", Replacement:="", LookAt:=xlPart
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
   
    With Range("A1:A" & LastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    With Range("B1:B" & LastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    With Range("C1:C" & LastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    With Range("D1:D" & LastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    With Range("T1:T" & LastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
End Sub
 

LorettaAlsop

New Member
Joined
Jun 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

ohhhhh, just seeing this, will try...
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
373
Office Version
  1. 365
Platform
  1. Windows
I should have put the LastRow calculation first like this:
VBA Code:
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart
    Range("T:T").Replace What:=" ", Replacement:="", LookAt:=xlPart
That way, if the last cell in column A happens to be filled with spaces, it gets marked as the last row rather than moving up to the last cell with actual data in it.
 

LorettaAlsop

New Member
Joined
Jun 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I should have put the LastRow calculation first like this:
VBA Code:
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart
    Range("T:T").Replace What:=" ", Replacement:="", LookAt:=xlPart
That way, if the last cell in column A happens to be filled with spaces, it gets marked as the last row rather than moving up to the last cell with actual data in it.
 

LorettaAlsop

New Member
Joined
Jun 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
This worked perfectly!

I not only got the code to run so slick this will be a HUGE time saver! I learned new things from you and had fun too! Thank you Soooooooooooo much!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,619
Messages
5,659,903
Members
418,536
Latest member
Tezzies

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