Defining Ranges using macros

[bhussey]

I need a way to automatically define a worksheet's range as well as
number of rows. The current recorded macro has a range of A1:A300 but
the value could change. Tomorrow I could have a range of A1:A50 or
A1:A550. I need to be able to accommodate a worksheet with any number
of rows.

Range("F1:L1").Select
Selection.AutoFill Destination:=Range("F1:L200")
Range("F1:L200").Select
Range("F1").Select
Columns("L:L").Select
Selection.Copy
Range("F33").Select
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Rows("1:2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("A1").Select
Sheets("ARPWVoid_120805").Select
Range("A1:A2").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A6").Select
ActiveWindow.ScrollRow = 9185
ActiveWindow.SmallScroll Down:=387
ActiveWindow.ScrollRow = 9837
ActiveWindow.SmallScroll Down:=141
Range("A200").Select
Sheets("ARPWVoid_120805").Select
ActiveWindow.ScrollRow = 40
Range("A70:A71").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A200").Select
Range("A1").Select

 

[tony h]

not my preferred way but probably the easiest for you to implement

at the beginning of the subroutine put in

dim intTotalRows as integer
intTotalRows= 200


then where you have range("L1200") or similar replace with
range("L1" cstr(intTotalRows))

then all you need to do is change the number in the one line at the
top.

If you want to make it cleaverer instead of intTotalRows = 200 use
intTotalRows=InputBox("Enter Max Rows") note that this will cause an
error if a non-number is entered.

Does this help?

 

[mudraker]

Decalare variable within Macro
LastRow As Long


To find last used row in a column use

LastRow = Cells(Rows.Count, "a").End(xlUp).Row

To find last used row on sheet use

LastRow = Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row

Example of how To use within Macro

replace
Selection.AutoFill Destination:=Range("F1:L200")
with
Selection.AutoFill Destination:=Range("F1:L" & LastRow)

 

[bhussey]

Where would a plug
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
and would a change anything to this script?

into

Sub Macro1()
'
Application.CutCopyMode = False
Range("F1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*100*(-1)"
Range("G1").Select
ActiveCell.FormulaR1C1
"=RIGHT(CONCATENATE(""00000000"",RC[-6]),10)"
Range("H1").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-6],""MMDDYY"")"
Range("I1").Select
ActiveCell.FormulaR1C1 = "=RC[-6]"
Range("J1").Select
ActiveCell.FormulaR1C1 = "=RC[-6]"
Range("K1").Select
ActiveCell.FormulaR1C1
"=RIGHT(CONCATENATE(""00000000"",RC[-5]),10)"
Range("L1").Select
ActiveCell.FormulaR1C1
"=CONCATENATE(RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
Range("F1").Select
Range("F1:L1").Select
Selection.AutoFill Destination:=Range("F1:L100" & LastRow)


'

'
End Su

 

[mudraker]

I do not like reading code in the FormulaR1C1 format so I have changed
your formulas to the format I prefer.
This does not change the actual formula function.

I have also modified the code to make it more efficient.

Try this macro

Sub Macro1()
'
Dim LastRow As Long
Application.CutCopyMode = False
Range("F1").Value = "=E1*100*(-1)"
Range("G1").Value = "=RIGHT(CONCATENATE(""00000000"",A1),10)"
Range("H1").Value = "=TEXT(B1,""MMDDYY"")"
Range("I1").Value = "=C1"
Range("J1").Value = "=D1"
Range("K1").Value = "=RIGHT(CONCATENATE(""00000000"",F1),10)"
Range("L1").Value = "=CONCATENATE(G1,H1,I1,J1,K1)"
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Range("F1:L1").AutoFill Destination:=Range("F1:L" & LastRow)

End Sub

 

[bhussey]

Thank you so much for you're help this was a life saver.