Add multiple new rows with different data

Status
Not open for further replies.
Z

zack8576

Active Member
Joined
Dec 27, 2021
Messages
271
Office Version
  1. 365
Platform
  1. Windows
I need add 3 rows to the end of an excel file, and fill columns A-D with certain values
I know the code below can add one row and fill them with values
VBA Code: 
 lr = Cells(Rows.Count, 1).End(xlUp).Row
Public lr As Long, lr2 As Long
  lr2 = lr + 1
    Cells(lr2, "A") = "One"
    Cells(lr2, "B") = "Two"
    Cells(lr2, "C") = "Three"
    Cells(lr2, "D") = "Four"

Is below the right way to add 3 new rows, and fill each cell in these rows with different values ?

VBA Code: 
 lr = Cells(Rows.Count, 1).End(xlUp).Row
Public lr As Long, lr2 As Long
  lr2 = lr + 3
    Cells(lr2, "A") = "One", "One", "Five"
    Cells(lr2, "B") = "Two", "Six", "Seven"
    Cells(lr2, "C") = "Three", "Eight", "Nine"
    Cells(lr2, "D") = "Four", "Ten", "Eleven"
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
How you do it will depend on where the data is coming from to populate the 3 rows ?
The below is not the most common way of doing it but given you are using hard coded values I was aiming more for something you would find more intuitive.
I don't know why your code always seems to have Public Variables, typically the variable is contained in and limited to the Sub.
I don't know what value your lr2 is adding, I have not used it below.

VBA Code: 
Sub EnterDataLast3Rows()
    Dim lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    With Columns("A:D")
            lr = lr + 1
        .Rows(lr) = Array("One", "Two", "Three", "Four")
            lr = lr + 1
        .Rows(lr) = Array("Five", "Six", "Seven", "Eight")
            lr = lr + 1
        .Rows(lr) = Array("Nine", "Ten", "Eleven", "Twelve")
    End With
End Sub
 
Upvote 0
Alex Blakenburg said:
How you do it will depend on where the data is coming from to populate the 3 rows ?
The below is not the most common way of doing it but given you are using hard coded values I was aiming more for something you would find more intuitive.
I don't know why your code always seems to have Public Variables, typically the variable is contained in and limited to the Sub.
I don't know what value your lr2 is adding, I have not used it below.

VBA Code: 
Sub EnterDataLast3Rows()
    Dim lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    With Columns("A:D")
            lr = lr + 1
        .Rows(lr) = Array("One", "Two", "Three", "Four")
            lr = lr + 1
        .Rows(lr) = Array("Five", "Six", "Seven", "Eight")
            lr = lr + 1
        .Rows(lr) = Array("Nine", "Ten", "Eleven", "Twelve")
    End With
End Sub
Click to expand...
Alex thanks for the reply. The reason why I always use Public variables is because this subroutine is part of a bigger macro, I used Public variables since the beginning of this macro project, and have been using them ever since. Below is a sneak peak at the variables that are already defined in this macro.
VBA Code: 
Public wb2 As Workbook 'declare variable used on line 18
    Public ws1 As Worksheet, ws2 As Worksheet 
    Public c As Range, c2 As Range, c3 As Range, c4 As Range, d As Range, rng As Range, rng2 As Range
    Public FileName As String, Liner As String, Brevard As String
    Public x As Long, y As Long, lr As Long, lr2 As Long, lr3 As Long, lr4 As Long, lr5 As Long, lr6 As Long, lr7 As Long, lr8 As Long, lr9 As Long, lr10 As Long, lr11 As Long, lr12 As Long, i As Long, i2 As Long, i3 As Long, i4 As Long, i5 As Long, i6 As Long, i7 As Long, i10 As Long, lastRow As Long 
    Public sr As Long, n As Long, rr As Long, Data As String, TargetCell As Range, v As String, s As String

back to my question though, the macro is in excel file 1, when I run the macro, it opens excel file 2 and starts to do a bunch of modifications in file 2.
(There are thousands of these "file 2", they are all unique in their own way)
part of the modification is adding several new rows to the end of file 2, the last of the new row before these 3 new rows is lr11

Purpose of the 3 new rows:
count the number of items containing keywords "Base", "Riser", "Cone", "Top Slab", AND contain "SANITARY" in column O, we will call this number as n

new row 1 needs to have these values in the columns A-K :
value from row above a period n-1 F25888A No space space space Purchased space Closure
new row 2 needs to have these values in the columns A-K :
value from row above a period (n-1) / 8 F25889A No space space space Purchased space Primer
new row 3 needs to have these values in the columns A-K :
value from row above a period Space F25890A No space space space Purchased space Wrapid-Seal

below is what I have so far for this subroutine
VBA Code: 
Public sr As Long, lr10 As Long, lr12 As Long, n As Long
Sub WrapidSeal()
    sr = 2
    n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
    Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
    
    With Columns("A:K")
            lr12 = lr10 + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", "n-1", "F25888A", "No", """", """", """", "Purchased", """", "Closure")
            lr12 = lr10 + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", "(n-1) / 8", "F25889A", "No", """", """", """", "Purchased", """", "Primer")
            lr12 = lr10 + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", """", "F25890A", "No", """", """", """", "Purchased", """", "Wrapid-Seal")
    End With
    
    If Cells(lr12, "C").Value Like "*0*" Then
        Cells(lr12, "D") = ","
    End If
End Sub
 
Upvote 0
zack8576 said:
Alex thanks for the reply. The reason why I always use Public variables is because this subroutine is part of a bigger macro, I used Public variables since the beginning of this macro project, and have been using them ever since. Below is a sneak peak at the variables that are already defined in this macro.
VBA Code: 
Public wb2 As Workbook 'declare variable used on line 18
    Public ws1 As Worksheet, ws2 As Worksheet
    Public c As Range, c2 As Range, c3 As Range, c4 As Range, d As Range, rng As Range, rng2 As Range
    Public FileName As String, Liner As String, Brevard As String
    Public x As Long, y As Long, lr As Long, lr2 As Long, lr3 As Long, lr4 As Long, lr5 As Long, lr6 As Long, lr7 As Long, lr8 As Long, lr9 As Long, lr10 As Long, lr11 As Long, lr12 As Long, i As Long, i2 As Long, i3 As Long, i4 As Long, i5 As Long, i6 As Long, i7 As Long, i10 As Long, lastRow As Long
    Public sr As Long, n As Long, rr As Long, Data As String, TargetCell As Range, v As String, s As String

back to my question though, the macro is in excel file 1, when I run the macro, it opens excel file 2 and starts to do a bunch of modifications in file 2.
(There are thousands of these "file 2", they are all unique in their own way)
part of the modification is adding several new rows to the end of file 2, the last of the new row before these 3 new rows is lr11

Purpose of the 3 new rows:
count the number of items containing keywords "Base", "Riser", "Cone", "Top Slab", AND contain "SANITARY" in column O, we will call this number as n

new row 1 needs to have these values in the columns A-K :
value from row above a period n-1 F25888A No space space space Purchased space Closure
new row 2 needs to have these values in the columns A-K :
value from row above a period (n-1) / 8 F25889A No space space space Purchased space Primer
new row 3 needs to have these values in the columns A-K :
value from row above a period Space F25890A No space space space Purchased space Wrapid-Seal

below is what I have so far for this subroutine
VBA Code: 
Public sr As Long, lr10 As Long, lr12 As Long, n As Long
Sub WrapidSeal()
    sr = 2
    n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
    Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
   
    With Columns("A:K")
            lr12 = lr10 + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", "n-1", "F25888A", "No", """", """", """", "Purchased", """", "Closure")
            lr12 = lr10 + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", "(n-1) / 8", "F25889A", "No", """", """", """", "Purchased", """", "Primer")
            lr12 = lr10 + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", """", "F25890A", "No", """", """", """", "Purchased", """", "Wrapid-Seal")
    End With
   
    If Cells(lr12, "C").Value Like "*0*" Then
        Cells(lr12, "D") = ","
    End If
End Sub
Click to expand...
found a mistake in the last one, updated code below
VBA Code: 
Public sr As Long, lr11 As Long, lr12 As Long, n As Long
Sub WrapidSeal()
    sr = 2
    n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
    Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
    
    With Columns("A:K")
            lr12 = lr11 + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", "n-1", "F25888A", "No", """", """", """", "Purchased", """", "Closure")
            lr12 = lr11 + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", "(n-1) / 8", "F25889A", "No", """", """", """", "Purchased", """", "Primer")
            lr12 = lr11 + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", """", "F25890A", "No", """", """", """", "Purchased", """", "Wrapid-Seal")
    End With
    
    If Cells(lr12, "C").Value Like "*0*" Then
        Cells(lr12, "D") = ","
    End If
End Sub
 
Upvote 0
Alex Blakenburg said:
How you do it will depend on where the data is coming from to populate the 3 rows ?
The below is not the most common way of doing it but given you are using hard coded values I was aiming more for something you would find more intuitive.
I don't know why your code always seems to have Public Variables, typically the variable is contained in and limited to the Sub.
I don't know what value your lr2 is adding, I have not used it below.

VBA Code: 
Sub EnterDataLast3Rows()
    Dim lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    With Columns("A:D")
            lr = lr + 1
        .Rows(lr) = Array("One", "Two", "Three", "Four")
            lr = lr + 1
        .Rows(lr) = Array("Five", "Six", "Seven", "Eight")
            lr = lr + 1
        .Rows(lr) = Array("Nine", "Ten", "Eleven", "Twelve")
    End With
End Sub
Click to expand...
See dropbox link for a "file 2" example that contains Wrapid-Seal

www.dropbox.com

2022080011 SANITARY.csv

Shared with Dropbox
www.dropbox.com www.dropbox.com
 
Upvote 0
I suspect that you don't have your head around using variables. Most coders will use Public variables very frugally, the likelihood of updating something and it impacting another piece of code somewhere else is very high, and it will be very hard to trace where the problem lies.

In the above code the whole idea was to increment the last row so you get
last row + 1, then last row + 2 then last row + 3.
You have lr12 = lr11 + 1 3 times, so if lr11 is 10 then lr12 is going to be 11 three times instead of being 11 then 12 then 13.

Being up to lr12 implies that you are working on 12 sheets at once and having to track the last row on each one, this is highly unlikely.

So in the above which is your last row counter. Whichever it is, say lr, the next lines should be lr = lr + 1, so if lr starts out as 10 it becomes 11,12,13.
 
Upvote 0
Alex Blakenburg said:
I suspect that you don't have your head around using variables. Most coders will use Public variables very frugally, the likelihood of updating something and it impacting another piece of code somewhere else is very high, and it will be very hard to trace where the problem lies.

In the above code the whole idea was to increment the last row so you get
last row + 1, then last row + 2 then last row + 3.
You have lr12 = lr11 + 1 3 times, so if lr11 is 10 then lr12 is going to be 11 three times instead of being 11 then 12 then 13.

Being up to lr12 implies that you are working on 12 sheets at once and having to track the last row on each one, this is highly unlikely.

So in the above which is your last row counter. Whichever it is, say lr, the next lines should be lr = lr + 1, so if lr starts out as 10 it becomes 11,12,13.
Click to expand...
thanks Alex. I have to admit, I am very new to VBA and programming in general and I am trying to learn as much as I can.
Sounds like the idea of using Public variables was a bad idea after all, I may have to go back and redefine at least some of these as private.

I am starting to understand your logic or using lr instead of lr 9, lr10, lr11 now
Now you got me questioning my method of adding rows to the end of file and dumping numbers in them.
can you take a look at the Middleman file link below and give me a few pointers ?
specifically from subroutine "EyeboltAndChains" to "Wrapidseal", these are the codes that are adding rows to the excel file.
(currently these are all working codes except for the Wrapidseal one)
What is a better way to redefine these lr values? Is there a better method for adding these rows?

www.dropbox.com

MIDDLEMAN.xlsb

Shared with Dropbox
www.dropbox.com www.dropbox.com
www.dropbox.com

2022080011 SANITARY.csv

Shared with Dropbox
www.dropbox.com www.dropbox.com
 
Upvote 0
The way it looks to me is that you need lr to stay the same being the end of your data rows before adding any new rows because you want your sum formulas only to apply to up to that point.

All you other routines in that group can use the same Last Row which I have called lrNewRows.
Dim it in each routine and recalculate it in each routine as in the first 2 lines of the sub below.
If the n in the output rows is meant to use the n in value from the "n =" line then you need to remove the quotation marks around them or it will just insert the text "n-1" and the text "(n-1) / 8".

Rich (BB code): 
Sub WrapidSeal()
    Dim lrNewRows As Long
    lrNewRows = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    sr = 2
    n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
    Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
    
    With Columns("A:K")
            lrNewRows = lrNewRows + 1
        .Rows(lrNewRows) = Array(Cells(lr, "A"), ".", "n-1", "F25888A", "No", """", """", """", "Purchased", """", "Closure")
            lrNewRows = lrNewRows + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", "(n-1) / 8", "F25889A", "No", """", """", """", "Purchased", """", "Primer")
            lrNewRows = lrNewRows + 1
        .Rows(lrNewRows) = Array(Cells(lr, "A"), ".", """", "F25890A", "No", """", """", """", "Purchased", """", "Wrapid-Seal")
    End With
    
    ' XXX I don't understand what you are intending with this. It is currently only going to apply to the very last row
    If Cells(lrNewRows, "C").Value Like "*0*" Then
        Cells(lrNewRows, "D") = ","
    End If
End Sub
 
Upvote 0
Alex Blakenburg said:
The way it looks to me is that you need lr to stay the same being the end of your data rows before adding any new rows because you want your sum formulas only to apply to up to that point.

All you other routines in that group can use the same Last Row which I have called lrNewRows.
Dim it in each routine and recalculate it in each routine as in the first 2 lines of the sub below.
If the n in the output rows is meant to use the n in value from the "n =" line then you need to remove the quotation marks around them or it will just insert the text "n-1" and the text "(n-1) / 8".

Rich (BB code): 
Sub WrapidSeal()
    Dim lrNewRows As Long
    lrNewRows = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    sr = 2
    n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
    Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
   
    With Columns("A:K")
            lrNewRows = lrNewRows + 1
        .Rows(lrNewRows) = Array(Cells(lr, "A"), ".", "n-1", "F25888A", "No", """", """", """", "Purchased", """", "Closure")
            lrNewRows = lrNewRows + 1
        .Rows(lr12) = Array(Cells(lr, "A"), ".", "(n-1) / 8", "F25889A", "No", """", """", """", "Purchased", """", "Primer")
            lrNewRows = lrNewRows + 1
        .Rows(lrNewRows) = Array(Cells(lr, "A"), ".", """", "F25890A", "No", """", """", """", "Purchased", """", "Wrapid-Seal")
    End With
   
    ' XXX I don't understand what you are intending with this. It is currently only going to apply to the very last row
    If Cells(lrNewRows, "C").Value Like "*0*" Then
        Cells(lrNewRows, "D") = ","
    End If
End Sub
Click to expand...
' XXX I don't understand what you are intending with this. It is currently only going to apply to the very last row
Click to expand...
The purpose of this code is just whenever column C in new rows 1 and 2 is 0, then value in D would be a comma (dont need to worry about row 3, the value in C will always be 1)

What would be the best way to modify this code to achieve this ?
 
Upvote 0
Try this:
VBA Code: 
Sub WrapidSeal()
    Dim lrNewRows As Long
    lrNewRows = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    sr = 2
    n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
    Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
    
    With Columns("A:K")
            lrNewRows = lrNewRows + 1
        .Rows(lrNewRows) = Array(Cells(lr, "A"), ".", n - 1, "F25888A", "No", """", """", """", "Purchased", """", "Closure")
        If Cells(lrNewRows, "C").Value = 0 Then
            Cells(lrNewRows, "D") = ","
        End If
            lrNewRows = lrNewRows + 1
        .Rows(lrNewRows) = Array(Cells(lr, "A"), ".", (n - 1) / 8, "F25889A", "No", """", """", """", "Purchased", """", "Primer")
        If Cells(lrNewRows, "C").Value = 0 Then
            Cells(lrNewRows, "D") = ","
        End If
            lrNewRows = lrNewRows + 1
        .Rows(lrNewRows) = Array(Cells(lr, "A"), ".", """", "F25890A", "No", """", """", """", "Purchased", """", "Wrapid-Seal")
    End With

End Sub
 
Upvote 0
Solution
Status
Not open for further replies.

Similar threads

J
  • Question Question
Append data to master file
Replies
6
Views
337
Alex Blakenburg
A
I
  • Solved
VBA to remove formulas with values causing wrong calculations
2
Replies
12
Views
867
Peter_SSs
Peter_SSs
G
  • Solved
Change Macro to import files from hyperlinks
2
Replies
12
Views
938
GarnesGambit
G
I
  • Question Question
Delete Rows based off criteria meeting
Replies
1
Views
420
ItalianPlatinum
I
S
  • Solved
Copy data from multiple sheets without formatting
Replies
5
Views
589
RudRud
R

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

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