Apply Macro to varying row lengths

Hi Folks,

Firstly apologies if this is a somewhat trivial matter, im new to the macro thing.
I tried recording a macro to a set of data which involved delimiting the data and setting up dates / totals etc; and everything worked fine.

However, ive saved the macro off to a personal work space as i need to use it with other excel files.
The problem is the other excel files are are of varying row sizes (ie the one on which the macro recorded was 270, some files may be 60 etc)
when i try to use the recorded macro on other sized files, i dont get expected results (ie it sums down to 270 as opposed to 60 etc). any way to rectify this?
Thanks in advance.
J.

There is many ways to solve this.

Post your macro code and explain what part you need modified and someone will modify it for you.

hi,
This is my macro: The issue is that it goes to range L266 & k266. I would like to set this to go to the end of the row (e.g. ctrl+down arrow) as not all my spreadsheets are consistent in row count.
Thanks,
J.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 08/12/2006 by GIT&T
'

'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(10, 1), Array(14, 1), Array(24, 1), _
Array(39, 1), Array(54, 4), Array(62, 4), Array(70, 1), Array(80, 1)), _
TrailingMinusNumbers:=True
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Header"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Pay Group"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Pay Code"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Staff No"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Forename"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Surname"
Range("G1").Select
ActiveCell.FormulaR1C1 = "From Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "To Date"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Amnt"
Range("J1").Select
ActiveCell.FormulaR1C1 = "YTD"
Rows("1:1").Select
Selection.Font.Bold = True
Range("A2").Select
Selection.End(xlToRight).Select
Range("K2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/100"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/100"
Range("I3").Select
Selection.End(xlDown).Select
Range("K266").Select
ActiveCell.FormulaR1C1 = "x"
Range("L266").Select
ActiveCell.FormulaR1C1 = "x"
Range("K266").Select
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("L2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1:L267").Select
Range("L2").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K2:L2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Cut
Range("I2").Select
ActiveSheet.Paste
Range("A1").Select
End Sub

Try this macro

I think I have modified to do what you require.

I have replaced a lot lines of code with with 1 or 2 lines that do the same thing.

I have also added in code to get last used row number and replaced your hard coded row number of 266 with a variable row number


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 08/12/2006 by GIT&T
'
' Modified By Mudraker www.excelforum.com 09/12/06
'
Dim iLastRow As Integer

'Text to columns
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(10, 1), Array(14, 1), Array(24, 1), _
Array(39, 1), Array(54, 4), Array(62, 4), Array(70, 1), Array(80, 1)), _
TrailingMinusNumbers:=True

Columns("G:H").EntireColumn.AutoFit
Rows("1:1").Insert Shift:=xlDown

'Add Headers
Range("a1:j1").Value = Array("Header", "Pay Group", _
"Pay Code", "Staff No", "Forename", "Surname", _
"From Date", "To Date", "Amnt", "YTD")

Rows("1:1").Font.Bold = True

'find last used row
iLastRow = Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row

'add formulas
Range("K2:l" & iLastRow).FormulaR1C1 = "=RC[-2]/100"

Application.CutCopyMode = False
Range("A1:L" & iLastRow).Copy
Range("A1:L" & iLastRow).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("K2:L" & iLastRow).Cut
Range("I2").Select
ActiveSheet.Paste

Range("A1").Select
End Sub

Thanks for your response folks.

I will give them a go and revert back.
J.

Issue resolved.
Thats right spot on what im looking for Mudraker.

Cheers mate.