M
ML0940
Hi
I am trying to insert a row into a named range and have it update the size
of another range so that they are both identical and make the data in each
range the same.
I think I am almost there but not quite.
Also the below code is a little more then needed but I was using it for some
tests.
Can anyone help me get to that result?
Also, I would love this to happen on a change event, "if" the named range is
changed
Any help is appreciated
Thanks!
Mark
Sub UpdateRange()
Dim Sh1bW As Range
Dim RowNums As Integer
Dim CurrRow As Long
Set Sh1bW = Range("Sh1billsW")
'If a row is added or deleted to/from the range, return the range row count
If Sh1bW.Rows.Count + 1 Then
RowNums = Sh1bW.Rows.Count
'MsgBox "Number of Rows = " & RowNums
Sh1bW.Rows.Select
End If
'Return the row and value of each cell in the range
For Each Cell In Sh1bW
CurrRow = Cell.Row
'MsgBox "Row " & CurrRow & " = " & vbCrLf & Cell.Value
'Debug.Print "Row " & CurrRow & " = " & vbCrLf & Cell.Value
Next Cell
MsgBox RowNums
Dim Sh1bM As Range
Set Sh1bM = Range("Sh1billsM")
Sh1bM.Rows.Resize = RowNums
For Each Cell In Sh1bW
Sh1bM.Cells.Value = Sh1bW.Cells.Value
Sh1bM.Rows.Select
Next Cell
MsgBox Sh1bM.Rows.Count
End Sub
I am trying to insert a row into a named range and have it update the size
of another range so that they are both identical and make the data in each
range the same.
I think I am almost there but not quite.
Also the below code is a little more then needed but I was using it for some
tests.
Can anyone help me get to that result?
Also, I would love this to happen on a change event, "if" the named range is
changed
Any help is appreciated
Thanks!
Mark
Sub UpdateRange()
Dim Sh1bW As Range
Dim RowNums As Integer
Dim CurrRow As Long
Set Sh1bW = Range("Sh1billsW")
'If a row is added or deleted to/from the range, return the range row count
If Sh1bW.Rows.Count + 1 Then
RowNums = Sh1bW.Rows.Count
'MsgBox "Number of Rows = " & RowNums
Sh1bW.Rows.Select
End If
'Return the row and value of each cell in the range
For Each Cell In Sh1bW
CurrRow = Cell.Row
'MsgBox "Row " & CurrRow & " = " & vbCrLf & Cell.Value
'Debug.Print "Row " & CurrRow & " = " & vbCrLf & Cell.Value
Next Cell
MsgBox RowNums
Dim Sh1bM As Range
Set Sh1bM = Range("Sh1billsM")
Sh1bM.Rows.Resize = RowNums
For Each Cell In Sh1bW
Sh1bM.Cells.Value = Sh1bW.Cells.Value
Sh1bM.Rows.Select
Next Cell
MsgBox Sh1bM.Rows.Count
End Sub