Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Autofill Column

Posted on 2015-02-07
5
Medium Priority
?
511 Views
Last Modified: 2016-02-10
Looking for sub routine that copies values from one cell down until it no longer sees empty cells. Then as sub routine continues copy NEW value from non-empty cell into empty cells below it until again it encounters yet another non-empty cell picking up third new value again and continuing on until it hits row 1000.

Column A
empty cell
empty cell
FIRST VALUE
empty cell
SECOND VALUE
empty cell
empty cell
empty cell
THIRD VALUE
empty cell
empty cell

RUNNING MACRO SHOULD RESULT IN THE FOLLOWING
Column A
empty cell
empty cell
FIRST VALUE
FIRST VALUE
SECOND VALUE
SECOND VALUE
SECOND VALUE
SECOND VALUE
THIRD VALUE
THIRD VALUE
THIRD VALUE

Recording macro using something like, selection.autofill destination:=Range(a3:a4); however i believe i need solution to use dynamic variables as empty cells will always differ.
0
Comment
Question by:o z
  • 2
  • 2
5 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 1000 total points
ID: 40596183
This will do it but are you sure you always want to go to 1000?

Sub FillBlanks()

Dim lngRow As Long
Dim strValue  As String

For lngRow = 1 To 1000
    If Cells(lngRow, 1) <> "" Then
        strValue = Cells(lngRow, 1)
    Else
        Cells(lngRow, 1) = strValue
    End If
Next
End Sub

Open in new window

0
 
LVL 11

Assisted Solution

by:Wilder1626
Wilder1626 earned 1000 total points
ID: 40596192
Hi

I'm using this below macro but Martin Liss code his also very good and smaller

  ' Turn off screen updating to improve performance
    Application.ScreenUpdating = False
    On Error Resume Next
    ' Look in column A
    With Columns(1)
        ' For blank cells, set them to equal the cell above
        .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
        'Convert the formula to a value
        .Value = .Value
    End With
    Err.Clear
    Application.ScreenUpdating = True

Open in new window

0
 

Author Closing Comment

by:o z
ID: 40596523
Martin, your solution worked for column A as I described. However, I was really working with column G. And you are correct, I really only needed solution to loop through until last row populated in column F.

Wilder, your solution worked perfectly as I was able to modify column number to specify G.

I appreciate your help equally and have one last question... what other sites would you suggest I familiarize myself with to pick up such  VBA skills. Thanks!
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40596751
You may want to look at this link below. This  could be a good start.

The other option would be to get a friend like Martin Liss beside you. He actually showed me a lot,  last couple of year. :-)

The link below will have cases + macro code + the Excel template with the macro + the video that goes with it.

Multiple hours of fun!!!!!!!

Free VBA Training Videos
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40596906
Here's my code modified to look at columns G and F.

Sub FillBlanks()

Dim lngRow As Long
Dim strValue  As String
Dim lngLastRowF As Long

' Find the last populated cell in column F
lngLastRowF = Range("F1048576").End(xlUp).Row

' If you want to stop at the line *before* the last cell in F then
' replace the "For" line with this
'For lngRow = 1 To lngLastRowF - 1
For lngRow = 1 To lngLastRowF
    ' Look at values in column G. The syntax of Cells is Cells(row number, column number)
    If Cells(lngRow, 7) <> "" Then
        strValue = Cells(lngRow, 7)
    Else
        Cells(lngRow, 7) = strValue
    End If
Next

End Sub

Open in new window


In any case I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question