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
Thanks SamDsouza
112
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
VBA Code:
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
112