Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable number of rows.
I have created this macro but it cuts off if I have too many rows. Is there a
way to make it run until it hits a blank cell in another column? Here is the code I have now. feel free to e-mail me if you need more information. Sub Macro7() ' ' Macro7 Macro ' ' Columns("I:I").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("H:H").EntireColumn.AutoFit Range("I2").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-8]=RC[-1],""Goto_View"",""Goto_View_External"")" Range("I2").Select Selection.Copy Range("H2").Select Selection.End(xlDown).Select Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Range("A60486").Select Selection.End(xlUp).Select Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "ACTION" Range("I1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ActiveWindow.ScrollRow = 60216 ActiveWindow.ScrollRow = 59474 ActiveWindow.ScrollRow = 57743 ActiveWindow.ScrollRow = 55270 ActiveWindow.ScrollRow = 51561 ActiveWindow.ScrollRow = 47109 ActiveWindow.ScrollRow = 42658 ActiveWindow.ScrollRow = 38578 ActiveWindow.ScrollRow = 34869 ActiveWindow.ScrollRow = 31407 ActiveWindow.ScrollRow = 28068 ActiveWindow.ScrollRow = 25101 ActiveWindow.ScrollRow = 23122 ActiveWindow.ScrollRow = 20402 ActiveWindow.ScrollRow = 18919 ActiveWindow.ScrollRow = 17558 ActiveWindow.ScrollRow = 16198 ActiveWindow.ScrollRow = 14838 ActiveWindow.ScrollRow = 13602 ActiveWindow.ScrollRow = 12365 ActiveWindow.ScrollRow = 11376 ActiveWindow.ScrollRow = 10263 ActiveWindow.ScrollRow = 9522 ActiveWindow.ScrollRow = 8409 ActiveWindow.ScrollRow = 7667 ActiveWindow.ScrollRow = 6801 ActiveWindow.ScrollRow = 5936 ActiveWindow.ScrollRow = 4699 ActiveWindow.ScrollRow = 3216 ActiveWindow.ScrollRow = 1732 ActiveWindow.ScrollRow = 372 ActiveWindow.ScrollRow = 1 Range("E1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("I2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""Goto_View"","""",RC[-1])" Range("I2").Select Selection.Copy Range("H2").Select Selection.End(xlDown).Select Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Selection.End(xlUp).Select Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "DEST. FILE" Range("I1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.End(xlUp).Select Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWindow.SmallScroll Down:=9 Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.LargeScroll ToRight:=1 Columns("W:W").Select Selection.Cut ActiveWindow.LargeScroll ToRight:=-1 Columns("A:A").Select ActiveSheet.Paste ActiveWindow.LargeScroll ToRight:=1 Range("T2").Select Selection.Copy Columns("T:T").Select Selection.Replace What:="ACROBAT_DEFAULT", Replacement:="NEW_WINDOW", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False Range("T3").Select Application.CutCopyMode = False Range("T1").Select ActiveWindow.LargeScroll ToRight:=-1 Range("A1").Select Selection.End(xlDown).Select Range("A1").Select Selection.End(xlDown).Select Rows("60488:60488").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Columns("A:A").Select Selection.SpecialCells(xlCellTypeBlanks).Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.ClearContents Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable number of rows.
It is hard to fix code like this. Her is a much improved macro. I made of
made a fgew mistakes but you should easily be able to fix the problems. Sub Macro7() ' ' Macro7 Macro ' ' Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("H:H").EntireColumn.AutoFit Range("I2").FormulaR1C1 = _ "=IF(RC[-8]=RC[-1],""Goto_View"",""Goto_View_External"")" LastRow = Range("I" & rows.count).end(xlup).Row Range("I2").Copy Destination:=Range("H2:H" & Lastrow) Range("I1").Copy Range("E1:E" & LastRow).PasteSpecial _ Paste:=xlPasteValues Columns("I:I").Delete Application.CutCopyMode = False Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("I2").FormulaR1C1 = "=IF(RC[-4]=""Goto_View"","""",RC[-1])" Range("I2").Copy Destination:=Range("H2:H" & LastRow) Range("I1:I" & LastRow).FormulaR1C1 = "DEST. FILE" Range("H1").copy destination:=Range("H1:H" & LastRow) Columns("I:I").Delete Columns("W:W").Cut Columns("A:A").Paste Columns("T:T").Replace _ What:="ACROBAT_DEFAULT", _ Replacement:="NEW_WINDOW", _ LookAt:=xlPart Rows(Lastrow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove set FirstBlankCell = Columns("A:A").SpecialCells(xlCellTypeBlanks) set LastCell = ActiveCell.SpecialCells(xlLastCell) Range(FirstBlankCell, LastCell).ClearContents End Sub "krigger228" wrote: I have created this macro but it cuts off if I have too many rows. Is there a way to make it run until it hits a blank cell in another column? Here is the code I have now. feel free to e-mail me if you need more information. Sub Macro7() ' ' Macro7 Macro ' ' Columns("I:I").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("H:H").EntireColumn.AutoFit Range("I2").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-8]=RC[-1],""Goto_View"",""Goto_View_External"")" Range("I2").Select Selection.Copy Range("H2").Select Selection.End(xlDown).Select Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Range("A60486").Select Selection.End(xlUp).Select Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "ACTION" Range("I1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ActiveWindow.ScrollRow = 60216 ActiveWindow.ScrollRow = 59474 ActiveWindow.ScrollRow = 57743 ActiveWindow.ScrollRow = 55270 ActiveWindow.ScrollRow = 51561 ActiveWindow.ScrollRow = 47109 ActiveWindow.ScrollRow = 42658 ActiveWindow.ScrollRow = 38578 ActiveWindow.ScrollRow = 34869 ActiveWindow.ScrollRow = 31407 ActiveWindow.ScrollRow = 28068 ActiveWindow.ScrollRow = 25101 ActiveWindow.ScrollRow = 23122 ActiveWindow.ScrollRow = 20402 ActiveWindow.ScrollRow = 18919 ActiveWindow.ScrollRow = 17558 ActiveWindow.ScrollRow = 16198 ActiveWindow.ScrollRow = 14838 ActiveWindow.ScrollRow = 13602 ActiveWindow.ScrollRow = 12365 ActiveWindow.ScrollRow = 11376 ActiveWindow.ScrollRow = 10263 ActiveWindow.ScrollRow = 9522 ActiveWindow.ScrollRow = 8409 ActiveWindow.ScrollRow = 7667 ActiveWindow.ScrollRow = 6801 ActiveWindow.ScrollRow = 5936 ActiveWindow.ScrollRow = 4699 ActiveWindow.ScrollRow = 3216 ActiveWindow.ScrollRow = 1732 ActiveWindow.ScrollRow = 372 ActiveWindow.ScrollRow = 1 Range("E1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("I2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""Goto_View"","""",RC[-1])" Range("I2").Select Selection.Copy Range("H2").Select Selection.End(xlDown).Select Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Selection.End(xlUp).Select Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "DEST. FILE" Range("I1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.End(xlUp).Select Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWindow.SmallScroll Down:=9 Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.LargeScroll ToRight:=1 Columns("W:W").Select Selection.Cut ActiveWindow.LargeScroll ToRight:=-1 Columns("A:A").Select ActiveSheet.Paste ActiveWindow.LargeScroll ToRight:=1 Range("T2").Select Selection.Copy Columns("T:T").Select Selection.Replace What:="ACROBAT_DEFAULT", Replacement:="NEW_WINDOW", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False Range("T3").Select Application.CutCopyMode = False Range("T1").Select ActiveWindow.LargeScroll ToRight:=-1 Range("A1").Select Selection.End(xlDown).Select Range("A1").Select Selection.End(xlDown).Select Rows("60488:60488").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Columns("A:A").Select Selection.SpecialCells(xlCellTypeBlanks).Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.ClearContents Range("A1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable number of rows.
You would be better off explaining what you are trying to do, there is so much un needed code there and far too many selects, i have shortened the code but i don't know what you are trying to achieve as you also perform needless actions, we could do with seeing you workbook with an explanation of what you want to do, here's the shortened code, it can be made smaller: Code: -------------------- Sub Macro7() ' ' Macro7 Macro ' ' Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("H:H").EntireColumn.AutoFit Range("I2").ActiveCell.FormulaR1C1 = _ "=IF(RC[-8]=RC[-1],""Goto_View"",""Goto_View_External"")" Range("I2").Copy Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "ACTION" Range("I1").Select Range(Selection, Selection.End(xlDown)).Copy Range("E1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Delete Shift:=xlToLeft Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("I2").FormulaR1C1 = "=IF(RC[-4]=""Goto_View"","""",RC[-1])" Selection.Copy Range("H2").Select Selection.End(xlDown).Select Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Selection.End(xlUp).Select Range("I1").FormulaR1C1 = "DEST. FILE" Range("I1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.End(xlUp).Select Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Delete Shift:=xlToLeft Columns("W:W").Cut Columns("A:A").Paste Range("T2").Copy Columns("T:T").Select Selection.Replace What:="ACROBAT_DEFAULT", Replacement:="NEW_WINDOW", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False Rows("60488:60488").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Columns("A:A").SpecialCells(xlCellTypeBlanks).Sele ct Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.ClearContents Range("A1").Select Application.CutCopyMode = False End Sub -------------------- krigger228;248740 Wrote: I have created this macro but it cuts off if I have too many rows. Is there a way to make it run until it hits a blank cell in another column? Here is the code I have now. feel free to e-mail me if you need more information. Code: -------------------- Sub Macro7() ' ' Macro7 Macro ' ' Columns("I:I").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("H:H").EntireColumn.AutoFit Range("I2").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-8]=RC[-1],""Goto_View"",""Goto_View_External"")" Range("I2").Select Selection.Copy Range("H2").Select Selection.End(xlDown).Select Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Range("A60486").Select Selection.End(xlUp).Select Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "ACTION" Range("I1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ActiveWindow.ScrollRow = 60216 ActiveWindow.ScrollRow = 59474 ActiveWindow.ScrollRow = 57743 ActiveWindow.ScrollRow = 55270 ActiveWindow.ScrollRow = 51561 ActiveWindow.ScrollRow = 47109 ActiveWindow.ScrollRow = 42658 ActiveWindow.ScrollRow = 38578 ActiveWindow.ScrollRow = 34869 ActiveWindow.ScrollRow = 31407 ActiveWindow.ScrollRow = 28068 ActiveWindow.ScrollRow = 25101 ActiveWindow.ScrollRow = 23122 ActiveWindow.ScrollRow = 20402 ActiveWindow.ScrollRow = 18919 ActiveWindow.ScrollRow = 17558 ActiveWindow.ScrollRow = 16198 ActiveWindow.ScrollRow = 14838 ActiveWindow.ScrollRow = 13602 ActiveWindow.ScrollRow = 12365 ActiveWindow.ScrollRow = 11376 ActiveWindow.ScrollRow = 10263 ActiveWindow.ScrollRow = 9522 ActiveWindow.ScrollRow = 8409 ActiveWindow.ScrollRow = 7667 ActiveWindow.ScrollRow = 6801 ActiveWindow.ScrollRow = 5936 ActiveWindow.ScrollRow = 4699 ActiveWindow.ScrollRow = 3216 ActiveWindow.ScrollRow = 1732 ActiveWindow.ScrollRow = 372 ActiveWindow.ScrollRow = 1 Range("E1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("I2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""Goto_View"","""",RC[-1])" Range("I2").Select Selection.Copy Range("H2").Select Selection.End(xlDown).Select Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Selection.End(xlUp).Select Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "DEST. FILE" Range("I1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.End(xlUp).Select Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWindow.SmallScroll Down:=9 Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.LargeScroll ToRight:=1 Columns("W:W").Select Selection.Cut ActiveWindow.LargeScroll ToRight:=-1 Columns("A:A").Select ActiveSheet.Paste ActiveWindow.LargeScroll ToRight:=1 Range("T2").Select Selection.Copy Columns("T:T").Select Selection.Replace What:="ACROBAT_DEFAULT", Replacement:="NEW_WINDOW", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False Range("T3").Select Application.CutCopyMode = False Range("T1").Select ActiveWindow.LargeScroll ToRight:=-1 Range("A1").Select Selection.End(xlDown).Select Range("A1").Select Selection.End(xlDown).Select Rows("60488:60488").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Columns("A:A").Select Selection.SpecialCells(xlCellTypeBlanks).Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.ClearContents Range("A1").Select End Sub -------------------- -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=69423 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable number of rows.
I wouldn't recoomend using the Select method. It is slow and is hard to
read. Look at my code, it is the recoomended way of coding. the macro recorder code is very cumbersome. I always mdoify any code that I use that comes from the macro recorder. "Simon Lloyd" wrote: You would be better off explaining what you are trying to do, there is so much un needed code there and far too many selects, i have shortened the code but i don't know what you are trying to achieve as you also perform needless actions, we could do with seeing you workbook with an explanation of what you want to do, here's the shortened code, it can be made smaller: Code: -------------------- Sub Macro7() ' ' Macro7 Macro ' ' Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("H:H").EntireColumn.AutoFit Range("I2").ActiveCell.FormulaR1C1 = _ "=IF(RC[-8]=RC[-1],""Goto_View"",""Goto_View_External"")" Range("I2").Copy Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "ACTION" Range("I1").Select Range(Selection, Selection.End(xlDown)).Copy Range("E1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Delete Shift:=xlToLeft Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("I2").FormulaR1C1 = "=IF(RC[-4]=""Goto_View"","""",RC[-1])" Selection.Copy Range("H2").Select Selection.End(xlDown).Select Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Selection.End(xlUp).Select Range("I1").FormulaR1C1 = "DEST. FILE" Range("I1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.End(xlUp).Select Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Delete Shift:=xlToLeft Columns("W:W").Cut Columns("A:A").Paste Range("T2").Copy Columns("T:T").Select Selection.Replace What:="ACROBAT_DEFAULT", Replacement:="NEW_WINDOW", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False Rows("60488:60488").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Columns("A:A").SpecialCells(xlCellTypeBlanks).Sele ct Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.ClearContents Range("A1").Select Application.CutCopyMode = False End Sub -------------------- krigger228;248740 Wrote: I have created this macro but it cuts off if I have too many rows. Is there a way to make it run until it hits a blank cell in another column? Here is the code I have now. feel free to e-mail me if you need more information. Code: -------------------- Sub Macro7() ' ' Macro7 Macro ' ' Columns("I:I").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("H:H").EntireColumn.AutoFit Range("I2").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-8]=RC[-1],""Goto_View"",""Goto_View_External"")" Range("I2").Select Selection.Copy Range("H2").Select Selection.End(xlDown).Select Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Range("A60486").Select Selection.End(xlUp).Select Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "ACTION" Range("I1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ActiveWindow.ScrollRow = 60216 ActiveWindow.ScrollRow = 59474 ActiveWindow.ScrollRow = 57743 ActiveWindow.ScrollRow = 55270 ActiveWindow.ScrollRow = 51561 ActiveWindow.ScrollRow = 47109 ActiveWindow.ScrollRow = 42658 ActiveWindow.ScrollRow = 38578 ActiveWindow.ScrollRow = 34869 ActiveWindow.ScrollRow = 31407 ActiveWindow.ScrollRow = 28068 ActiveWindow.ScrollRow = 25101 ActiveWindow.ScrollRow = 23122 ActiveWindow.ScrollRow = 20402 ActiveWindow.ScrollRow = 18919 ActiveWindow.ScrollRow = 17558 ActiveWindow.ScrollRow = 16198 ActiveWindow.ScrollRow = 14838 ActiveWindow.ScrollRow = 13602 ActiveWindow.ScrollRow = 12365 ActiveWindow.ScrollRow = 11376 ActiveWindow.ScrollRow = 10263 ActiveWindow.ScrollRow = 9522 ActiveWindow.ScrollRow = 8409 ActiveWindow.ScrollRow = 7667 ActiveWindow.ScrollRow = 6801 ActiveWindow.ScrollRow = 5936 ActiveWindow.ScrollRow = 4699 ActiveWindow.ScrollRow = 3216 ActiveWindow.ScrollRow = 1732 ActiveWindow.ScrollRow = 372 ActiveWindow.ScrollRow = 1 Range("E1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("I2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""Goto_View"","""",RC[-1])" Range("I2").Select Selection.Copy Range("H2").Select Selection.End(xlDown).Select Range("I60487").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Selection.End(xlUp).Select Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "DEST. FILE" Range("I1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.End(xlUp).Select Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWindow.SmallScroll Down:=9 Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("I:I").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.LargeScroll ToRight:=1 Columns("W:W").Select Selection.Cut ActiveWindow.LargeScroll ToRight:=-1 Columns("A:A").Select ActiveSheet.Paste ActiveWindow.LargeScroll ToRight:=1 Range("T2").Select Selection.Copy Columns("T:T").Select Selection.Replace What:="ACROBAT_DEFAULT", Replacement:="NEW_WINDOW", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False Range("T3").Select Application.CutCopyMode = False Range("T1").Select ActiveWindow.LargeScroll ToRight:=-1 Range("A1").Select Selection.End(xlDown).Select Range("A1").Select Selection.End(xlDown).Select Rows("60488:60488").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Columns("A:A").Select Selection.SpecialCells(xlCellTypeBlanks).Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.ClearContents Range("A1").Select End Sub -------------------- -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=69423 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the Number of Variable Rows | Excel Programming | |||
Linking to a Variable Number of Rows - XP/07 | Excel Worksheet Functions | |||
insert variable number of rows | Excel Programming | |||
Paste to variable number of rows | Excel Programming | |||
Paste to variable number of rows | Excel Programming |