Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA Autofill Column

Posted on 2015-02-07
5
Medium Priority
?
474 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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