Macros for Autofilling Columns Based on Last Cell

tinydancer

New Member
Joined
Jun 15, 2016
Messages
44
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
This should work:
Test it though, make sure its filling in the correct names where it should be:

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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
Back
Top