Filling empty cells in a column

[tazz]

Hello,
I need help with a macro that will fill all empty cells in a column(say BA)between BA2 and the last filled cell in the column.
The filled cells could have value "Filled"(or any other value )
Thank you for your help.

 

[crouchsw]

activesheet.range("BA2").select
if selection.value = "" then 'just in case BA2 is empty - I assume you want it filled as well, if it is
     selection.value = "Blah" 'or whatever you want here
End if
loop1:
     selection.offset(1,0).select
     if selection.value = "" then
          selection.value = "Blah"
          goto loop1
     End if
end sub

 

[SirJB7]

Hi, tazz!
Try this:

Option Explicit

Sub Feeling()
  ' constants
  Const kscolumn = "BA"
  Const ksFiller = "Cobol"
  ' declarations
  Dim rng As Range
  ' start
  Set rng = ActiveSheet.Columns(kscolumn)
  ' process
  With rng
  On Error GoTo Feeling_Exit
  Range(.Rows(2), .Rows(.Rows.count).End(xlUp)).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = ksFiller
  .Cells(1, 1).Select
Feeling_Exit:
  End With
  ' end
  Set rng = Nothing
  Beep
End Sub

The difference against crouchsw's method is that while he loops one time for each cell, I process all cells at a time; if the range is huge, the performance is much faster in 2nd case.
Regards!

 

[tazz]

Hello SirJB,
This code is working really nice. Is there a way to make it stop if there are no empty cells?
Thank you.

@crouchsw
I also tested your code but for some reason is filling only cell 3.
Thank you for the head start

 

[SirJB7]

Hi, Tazz!
Done, code fixed and updated in previous post. Added a On Error sentence. Tested and worked fine. BTW, it raises an error and actually stopped running, it didn't kept on executing as I understood from your post.
Regards!

 

[Smallman]

Guys

Keeping it simple.

Sub Testo()
On Error Resume Next
  Range("BA2", Range("BA" & Rows.Count).End(xlUp)).SpecialCells(4) = "Fillme"
On Error GoTo 0
End Sub

Smallman

 

[tazz]

Guys,
Both of your macros are working perfect.
Thank you so much.

 

[SirJB7]

Hi, tazz!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!