Apply Macro to multiple subsequent rows

travieze · Mar 28th 2021

Hey Guys,

Im new to VBA and I want to apply a code then I've written to store values in subsequent rows as I click a button.

I've created the following sub for only one particular row, but I would like to apply it further down the list (basically the whole of column F).

Now it is only working for the first row that is 'Pork'.

Code

Sub RunMe()
For Each Cell In Range("F2:XFD2")
If Cell.Value = vbNullString Then
Cell.Value = Range("F2")
Exit Sub
End If
Next Cell
End Sub

I've also attached the working file for reference! Do hope you guys can help me out. Thank you

**Carim** · Mar 28th 2021

Hello and Welcome to the Forum

Not sure to understand what your objective is ...

Your title is about rows ... and your macro is about columns ...

Should you need to fill in the blank cells ...

Code

Sub TestFillBlanks()
    Range("F2:X2").SpecialCells(xlCellTypeBlanks).Value = Range("F2").Value
End Sub

Hope this will help

**royUK** · Mar 28th 2021

What are you trying to do? Your code is filling

You don't need the Exit Sub

**royUK** · Mar 28th 2021

Maybe you mean

Code

For lRw = 2 To ActiveSheet.UsedRange.Rows.Count
 Range("F" & lRw & ":XFD" & lRw).SpecialCells(xlCellTypeBlanks).Value = Range("F" & lRw).Value
 Next lRw

**royUK** · Mar 28th 2021

You need an error handler in case no empty cells are found

Code

Sub RunMe()
    Dim lRw As Long
    
    For lRw = 2 To ActiveSheet.UsedRange.Rows.Count
        On Error Resume Next
        Range("F" & lRw & ":XFD" & lRw).SpecialCells(xlCellTypeBlanks).Value = Range("F" & lRw).Value
        On Error GoTo 0
    Next lRw
End Sub

travieze · Mar 28th 2021

Hey Guys,

Really Appreciate your responses! Sorry I wasn't clear on my request, I was trying to store the balance value in the next column on every single click of a button.

The code works really well Roy.Thank you!

However, could it be done a column at a time on the click of a button? The date at row 1 was supposed to appear when I store a new column value.

Code

Sub RunMe()

Dim lRw As Long



For lRw = 2 To ActiveSheet.UsedRange.Rows.Count

On Error Resume Next

Range("F" & lRw & ":XFD" & lRw).SpecialCells(xlCellTypeBlanks).Value = Range("F" & lRw).Value

On Error GoTo 0

Next lRw

End Sub

Display More

**royUK** · Mar 28th 2021

I can't understand what you are trying to do.

Row 1 s headers. Do you want to add new dates? In which case are they month ends and why are using TODAY() in G1. TODAY is volatile and will change every day.

travieze · Mar 28th 2021

Okay I'll try to explain my issue again, real sorry about that.

Im tracking my inventory balance daily for various meats at the end of each day.

Currently, I want to store all values of column F (balance stock) in column G using a macro button. I've added an IF function that gives TODAY date when the value in stored in column G1.

I hope that whenever I adjust the balance stock the following day in column F and click the button, it will save the new balance in another column H. Then the date in H1 will update accordingly.

I aim to do this moving forward, and my initial issue was not knowing how to apply my code to the whole of Column F (which will have Pork, Chicken, Beef etc.) I could only apply my code to the "Pork" row even though I've achieved the storing of values portion.

I hope this is a little clearer!

**Carim** · Mar 28th 2021

Hello,

May be attached file will help

**royUK** · Mar 28th 2021

TODAY is volatile, therefore it will change every day.

**royUK** · Mar 28th 2021

Try this

Code

Sub RunMe()
    Dim rCl As Range
    Set rCl = Cells(Columns.Count).End(xlToLeft).Offset(, 1) '
   With rCl
   .Value = Format(Date, "Short Date")
   .Offset(1).Value = Range("F2").Value
   End With
End Sub

**royUK** · Mar 28th 2021

This is possibly what you want

Code

Sub RunMe()
    Dim rCl As Range
    Set rCl = Cells(Columns.Count).End(xlToLeft).Offset(, 1) '
    With rCl
        .Value = Format(Date, "Short Date")
        .Offset(1).Resize(3, 1) = Range("F2:F4").Value
    End With
End Sub

travieze · Mar 28th 2021

Thank you guys so much for helping me out royUK & Carim!

royUK this code is what I was looking for and more. Really appreciate your expertise and insights.

Code

Sub RunMe()
Dim rCl As Range
Set rCl = Cells(Columns.Count).End(xlToLeft).Offset(, 1) '
With rCl
.Value = Format(Date, "Short Date")
.Offset(1).Resize(3, 1) = Range("F2:F4").Value
End With
End Sub

**royUK** · Mar 28th 2021

If you want add ore rows then this will be dynamic

Code

Sub RunMe()
    Dim rCl As Range
    Dim iX As Integer
    Set rCl = Cells(Columns.Count).End(xlToLeft).Offset(, 1)
    iX = Range("F2").End(xlDown).Row
    With rCl
        .Value = Format(Date, "Short Date")
        .Offset(1).Resize(iX - 1, 1) = Range("F2:F" & iX).Value
    End With
End Sub

**royUK** · Mar 28th 2021

Pleased to help

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Note: no apostrophe in the tags, just used for demonstration here.

['code]

your code goes between these tags

['/code]

Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

travieze · Mar 28th 2021

That's amazing, thanks once again!

And I'll be sure to include the code tags for future posts

**royUK** · Mar 28th 2021

Glad you like it

Apply Macro to multiple subsequent rows

Participate now!

Share

Tags