Auto Fill From Bottom of Range

L

lojobo

New Member
Joined
May 13, 2011
Messages
1
The code that I'm using works but only fills in one empty cell at a time and my data range is kinda big and i would have to run the code about a 1K inorder to fill in all the blanks. :confused:

Code: 
Sub fillArowsIfBlank()
Dim mytest As Range
Set mytest = Range(Range("A69999"), Range("A2").End(xlUp))

For Each Datafill In mytest
    If Datafill = "" Then Datafill.Value = Datafill.Offset(1, 0).Value
Next Datafill
Application.ScreenUpdating = Paste
ActiveCell.SpecialCells(xlLastCell).Select
 
End Sub


<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 203.25pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=271><TBODY><TR style="HEIGHT: 30pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 81.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 30pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt" vAlign=top width=109 noWrap>The data looks like this<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 121.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 30pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom width=162 noWrap>want it to fill from bottom and look like this<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 81.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt" vAlign=top width=109 noWrap></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 121.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=162 noWrap>5832<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 81.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt" vAlign=top width=109 noWrap></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 121.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=162 noWrap>5832<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 81.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt" vAlign=top width=109 noWrap>5832<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 121.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=162 noWrap>5832<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 81.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt" vAlign=top width=109 noWrap></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 121.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=162 noWrap>4505<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 81.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt" vAlign=top width=109 noWrap>4505<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 121.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=162 noWrap>4505<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 81.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt" vAlign=top width=109 noWrap></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 121.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=162 noWrap>5813<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 81.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt" vAlign=top width=109 noWrap></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 121.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=162 noWrap>5813<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 81.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt" vAlign=top width=109 noWrap></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 121.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=162 noWrap>5813<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 9; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 81.75pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt" vAlign=top width=109 noWrap>5813<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 121.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=162 noWrap>5813<o:p></o:p>
</TD></TR></TBODY></TABLE>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Give this code a try...
Code: 
Sub FillInTheBlanks()
  Dim Area As Range, LastRow As Long, CellRange As Range
  Const ColLetter As String = "A"
  Const DataStartRow As Long = 2
  LastRow = Cells(Rows.Count, ColLetter).End(xlUp).Row
  Set CellRange = Intersect(Rows(DataStartRow & ":" & LastRow), Columns(ColLetter))
  On Error Resume Next
  For Each Area In CellRange.SpecialCells(xlCellTypeBlanks).Areas
    Area.Value = Area.Rows(Area.Rows.Count).Offset(1).Value
  Next
End Sub
Just change the ColLetter constant (the first Const statement) to the column you want to fill and change the DataStartRow constant (the second Const statement) to the first data row (the one you want to fill up to; that is, Row 1 unless you have headers, in which case it would be the first row after the headers).
 
Upvote 0
Another
Code: 
Sub fillup()
Dim e As Range
Set e = Cells(Rows.Count, 3).End(3)
Do
   Range(e, e.End(3)(2)) = e
   Set e = e.End(3)
Loop Until e.Row = 1
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
574
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
913
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,223,954
Messages
6,175,600
Members
452,658
Latest member
GStorm

Share this page

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