Want to get New Row position of firstRow of Range after combining two or multiple ranges repeatedly

[SamDsouza]

Hello
How can i get new First Row position for New Range.
on basis of Rows.Count to some extent i was able to combine Two Ranges at a particular Rows.
But to get the same 3 or 5 times at every NewRow Position for two ranges as per below coding repeating it seemed bit difficult
Also if more ranges added then Rows.Count for more ranges could differ and may not be the same . Your suggestion will be appreciated

Private Sub Workbook_Open()
Dim lastRow As Long, rowsCnt As Long, i As Integer, FirstRowRange As Long, EndRowRange As Long, FirstNewRowRange As Long ', firstRow As Long
Dim lastNewRowRange As Long, newrowsCnt As Long, j As Integer, xFirstRow As Long
Dim firstRow as Long:  firstRow = 4

Dim wks As Worksheet
Set wks = Worksheets("Sheet1")
 
rowsCnt = wks.Range("A4:F9").Rows.Count
newrowsCnt = wks.Range("A10:F17").Rows.Count

EndRowRange = firstRow + rowsCnt - 1
FirstNewRowRange = EndRowRange + 1
lastNewRowRange = FirstNewRowRange + newrowsCnt - 1

MsgBox "Rows Count From A4:F9 = " & rowsCnt & " New Rows Range Count from A10:F17 = " & newrowsCnt & vbCrLf & _
"First Row : " & firstRow & "  Last Row " & EndRowRange & vbCrLf & _
"FirstNewRowRange : " & FirstNewRowRange & "  LastNewRowRange : " & lastNewRowRange

For i = firstRow To EndRowRange
      wks.Range("B" & i & ":F" & i).Value = "SAM"
Next i

For i = FirstNewRowRange To lastNewRowRange
      wks.Range("B" & i & ":F" & i).Value = "DSOUZA"
Next i
lastNewRowRange = lastNewRowRange + 1

End Sub

Thanks SamDsouza
112

 

[SamDsouza]

DanteAmor Now Don't bother to reply as i've resolved. Thanks to Dave and you without your valuable inputs i would have been still struggling.
??
Thank you so much guys

this syntax made the difference
wks.Range("B" & nFila + a).Resize(8, 5).Value = "DAVE"

Folllowing is the Resolved code

Public Sub DanteAmor_Solution()

Dim wks As Worksheet, ary As Variant, a As Variant, nFila As Long
Set wks = Worksheets("Sheet1")

wks.Cells.ClearContents
wks.Cells.ClearFormats

ary = Array(6, 3, 4)
nFila = 4

For Each a In ary
    wks.Range("B" & nFila).Resize(a, 5).Value = "SAM"
    wks.Range("B" & nFila + a).Resize(8, 5).Value = "DAVE"
   
    nFila = nFila + a + 8
Next
End Sub

Thanks
SamD
118

 

[DanteAmor]

wks.Range("B" & nFila + a).Resize(8, 5).Value = "DAVE"

I did not understand what happened.
That line was like that since the original post of my macro.

Glad to know that it already works for you. Thanks for the feedback.