Range Commands in Macros

[Cindy Mikeworth]

I have attempted to create a column insertion macro that dynamically copies
a static value of 'Active' into all the rows in the range. To this end,
during recording, I move to the column to the left and use an END-DOWN.
However, when I replay the macro, it copies the values to the static number
of rows that existed during the recording rather than the number of rows in
the current sheet.

My macro is below. I can see where it references cell B85. Can anyone help
me with how to get this to work?

Sub InsertStatusReason()
'
' InsertStatusReason Macro
' Macro recorded 09/25/2006 by cindy
'

'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Selection.End(xlDown).Select
Range("B85").Select
ActiveCell.FormulaR1C1 = "Active"
Range("B85").Select
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlUp).Select
ActiveCell.FormulaR1C1 = "STATUS REASON"
Range("B2").Select
End Sub

 

[Dave Peterson]

I think that this does it:

Option Explicit
Sub InsertStatusReason()

Dim LastRow As Long
With ActiveSheet
.Columns("B:B").Insert
LastRow = .Range("A1").End(xlDown).Row
.Range("b2:B" & LastRow).Value = "Active"
.Range("B1").Value = "Status Reason"
End With

End Sub

 

[Bernie Deitrick]

Cindy,

Sub InsertStatusReason2()
Columns("B:B").Insert Shift:=xlToRight
Range("B2", Range("A1").End(xlDown)(1, 2)).Value = "Active"
Range("B1").Value = "STATUS REASON"
End Sub

HTH,
Bernie
MS Excel MVP

 

[Trevor Shuttleworth]

One way:

Range(Range("B1"), _
Range("B1").Offset(0, -1).End(xlDown).Offset(0, 1)).Value = _
"Active"

puts "Active" in all the cells in column B down to the last non-blank cell
in column A. It does assume there are no gaps in the data in column A.

If that can't be guaranteed, try:

Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Range("B1:B" & LastRow).Value = "Active"

Or, a little neater:

Range("B1:B" & Range("A65536").End(xlUp).Row).Value = "Active"

Regards

Trevor

 

[Cindy Mikeworth]

Thanks folks!