MACRO TO AUTOFILL W/ SPECIAL CHARACTER TILL LAST ROW OF DATA (not fill from text above)

[JuicyMusic]

Good morning,
I have a PDF that I'm converting to Excel. All the data gets populated in column A. The last row of data varies.

I usually use the Ctrl-G autofill formula to copy the data from the row above. I need a different autofill formula. I would prefer a Macro to run on whatever column I am highlighting.

Is there a VBA to insert whatever type of text I'd like in the blank cells....till the last row of data? For example, I'd like two asterisks (**) to be filled in every blank cell in column A (or whatever column I have made active.

Thank you so much,
Juicy

 

[mumps]

Select the desired range and try this macro:

Sub FillBlankCells()
    Application.ScreenUpdating = False
    Selection.Replace "", "**"
    Application.ScreenUpdating = True
End Sub

 

[James006]

Hi,
As an example ... just double-click

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim st As String, lastr As Long
st = "**"
lastr = Cells(Rows.Count, "A").End(xlUp).Row
' For any Column ...
Range(Cells(1, Target.Column), Cells(lastr, Target.Column)).SpecialCells(xlCellTypeBlanks).Value = st
Cancel = True
End Sub

 

[Fluff]

Another option

Sub juicymusic()
   On Error Resume Next
   Selection.SpecialCells(xlBlanks).Value = "**"
   On Error GoTo 0
End Sub

 

[JuicyMusic]

Select the desired range and try this macro:

Sub FillBlankCells()
    Application.ScreenUpdating = False
    Selection.Replace "", "**"
    Application.ScreenUpdating = True
End Sub

IT worked perfectly for me. I see the two screen updating lines so it doesn't flash while the code is running. Could you let me know why there are two lines like that?
Just wanting to learn even though it worked perfectly.

 

[JuicyMusic]

Another option

Sub juicymusic()
   On Error Resume Next
   Selection.SpecialCells(xlBlanks).Value = "**"
   On Error GoTo 0
End Sub

Fluff, yours worked as well. I added them both to a module. I see what you did there. Shortened the code and no line to stop flashing.
I really wanted this code because the PDF to Excel conversion results in so many blank rows between pages that I couldn't get any data back when I set a filter on the header row. I love it!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JuicyMusic]

Hi,
As an example ... just double-click

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim st As String, lastr As Long
st = "**"
lastr = Cells(Rows.Count, "A").End(xlUp).Row
' For any Column ...
Range(Cells(1, Target.Column), Cells(lastr, Target.Column)).SpecialCells(xlCellTypeBlanks).Value = st
Cancel = True
End Sub

Hi, where should I click?...on the first blank cell?

 

[James006]

You can double-click on any cell in any column ...

 

[JuicyMusic]

You can double-click on any cell in any column ...

Oooo La La....I'm trying it right now!