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("L1:P200") or similar replace with range("L1:P" 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 I 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 Sub

[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.