Macros for Autofilling Columns Based on Last Cell

[tinydancer]

I am trying to write a macros so that a given column will autofill a given range based on the last cell. Below is an example to better explain what I need:

Michael



Michael
Richard

Richard
Tyler
Sam

Sam
Billy
Billy
Chad


Chad



Pretend these given names are all in the same column (lets say column A) in excel and the spaces between them represent blank cells. I need those blank cells to autofill with the given names at the top and bottom cell. Some of the cells only take up one or two cells as shown and therefore will not need to be touched. I'm having difficulty because the blank cells range in length and I also need it to stop on the last name in the column where ever that may be (last cell is not specified). If anyone could help I would be appreciated, this is for an internship I have.

 

[HERMONES]

This will work, however you need to add in a Do and a Loop.
As I dont know what the last cell will be on your sheet I haven't added this in.

Sub Namecheck()


Application.ScreenUpdating = False


Worksheets.Item(1).Activate
Range("A1").Select


Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select


ActiveCell.Offset(-1, 0).Copy
ActiveCell.PasteSpecial




End Sub

 

[tinydancer]

If the last cell in the column were to be let's say A40, then what would the Do and Loop look like? I'm having trouble coming up with the right format as it still won't run correctly. Thanks for the code I really appreciate it.

 

[Ranitha]

Hi, Why do you need macro for this. You can do it with simple excel technique.

Select the range/Column. Press Ctrl + G (It takes you to Go to window), click on special, choose blanks, click ok.
Blank cells will be highlighted.
Now press = ['up arrow' key once] then press CTRL +ENTER (Don't press enter alone)
Now select entire column, copy and paste as values.

 

[tinydancer]

That worked fantastically, thanks!!! And like I said this is for an internship, I have a boss that likes to make everything complicated and he loves macros. So if the coding can still be done that would be awesome, but I'll definitely show him this.

 

[HERMONES]

This should work:
Test it though, make sure its filling in the correct names where it should be:

Sub Namecheck()

Application.ScreenUpdating = False

Do Until ActiveCell.Value = "A40"

Worksheets.Item(1).Activate
Range("A1").Select


Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

ActiveCell.Offset(-1, 0).Copy
ActiveCell.PasteSpecial

Loop


End Sub

 

[Ranitha]

Another Method,

Sub FillBlanks()


Range("A1").Activate
Lastrow = Range("A65000").End(xlUp).Row


a = ActiveCell.Value


Do Until ActiveCell.Row > Lastrow
If ActiveCell.Value = "" Then
ActiveCell.Value = a
Else
a = ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Activate
Loop


End Sub

 

[tinydancer]

Thank you so much Ranitha that worked perfectly. Couldn't have asked for a better solution.

 

[tinydancer]

HERMONES I feel like this is close, but I kept getting caught in an infinite loop and had to keep ending the task. Thank you though.