Avatar of Euro5
Euro5Flag for United States of America

asked on 

VBA copy down rows

New Sample.xlsx 

I have to copy down rows in a way very similar to the previous code that copies down columns A & B.
For this project, I need to do the same with columns A, B, C D in the same way.
D copies down until not empty or C changed.
C copies down until not empty or B changed.
B copies down until not empty or A changed.
A copies down until not empty OR until K is empty.

The sample file shows the original from A - E  and what it should look like completed is F - K.
Please - can anyone help?! Thank you!
Sub Filldownrows()
    Worksheets("Sheet1").Activate
Dim lngLastRow As Long
Dim lngRow As Long
Dim strSubcat As String
Dim strCat As String

With ActiveSheet
    strCat = .Range("A2")
    strSubcat = .Range("B2")
    lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    For lngRow = 3 To lngLastRow
        If Not IsEmpty(.Cells(lngRow, "A")) Then
            If .Cells(lngRow, "A") <> strCat Then
                strCat = .Cells(lngRow, "A")
                strSubcat = ""
            End If
        End If
        
        If IsEmpty(.Cells(lngRow, "B")) Then
            .Cells(lngRow, "A") = strCat
            .Cells(lngRow, "B") = strSubcat
        Else
            strSubcat = .Cells(lngRow, "B")
            .Cells(lngRow, "A") = strCat
        End If
    Next
End With
End Sub

Open in new window

VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Did you try my formula suggestion from your original question.

Use the same formula, just copy across another two columns:

in F =IF(A2="",F1,A2)
in G =IF(B2="",G1,B2)
in H =IF(C2="",H1,C2)
in I =IF(D2="",I1,D2)  

Copy down as far as required, copy and paste values back to columns A to D
Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

Thanks, Rob but I need a VBA solution. I will try to build on existing.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

The attached workbook shows the results of merely coping down the values in each column independently. In other words in column C for example, "60210 · Gasoline Petrol" is copied down until a different value is found in column C and then that value is copied down, etc. If that's not correct, please show me what the correct results should be for, say, rows 72 to 76. Or please supply a worksheet with 25 rows or so that shows before and after results.
Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

That sounds perfect, Martin! I will take a look!
Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

Martin, I don't see a workbook?
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Once again you’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Distinguished Expert in Excel 2018
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
              Experts Exchange Top Expert VBA 2018 to 2020
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo