Translation please :-)

[rmays]

ActiveCell.Offset(-5, 0).Rows("1:1").EntireRow.Select

This row of my recorded macro is highlighted when the debug runs. I am learning by trial and error (painful but permanent learning). I need to know how to read this line...ActiveCell seems obvious, but offset??? and the coordinates??? I think I understand EntireRow.Select, but if it isn't the obvious then let me know please. Any translation that might help me understand why this is stopping my macro would be appreciated. Thanks in advance. R.Mays

 

[MrKowz]

What offset does is it looks at the cell (in this case activecell) and moves the selection x rows and y columns.

Example:

If your activecell is B3, then Offset(3,0) will put you at B6 (B(3+3))
If your activecell is B3, then Offset(-2,1) will put you at C1 ((B+1)(3-2))

Rows("a:b") will select all rows between a and b

If your code isn't obscenely long, post it here and I will see what I can debug with it. Also, let me know what you are trying to accomplish with the code. (and please be sure to use

 tags)

 

[al_b_cnu]

Hi,

Looks like it's selecting the entire row of the currently active cell's row minus 5. So if cell C8 is currently selected, this will select row 3, and if cell c2 is selected, it will bomb out!

 

[rmays]

Below is the entire code for my macro and all I am really doing is formatting a spreadsheet from LARGE to APPLICABLE info.

by the way, the binary joke made me laugh outloud


Selection.Sort Key1:=ActiveCell.Offset(0, 3).Range("A1"), Order1:= _
xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="CA"
ActiveCell.Offset(12, 0).Rows("1:39").EntireRow.Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=4
ActiveWindow.SmallScroll ToRight:=12
ActiveCell.Offset(0, 16).Columns("A:E").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.LargeScroll ToRight:=-2
ActiveCell.Offset(0, -16).Range("A1").Select
ActiveCell.FormulaR1C1 = "CUSTOMER"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "ADDRESS"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "ST"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "ZIP"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "BRANCH"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "ACCT #"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "PRODUCT"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "DESCRIPTION"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "DOCUMENT"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "SRV DATE"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "QTY"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "CHAIN"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Cells.Select
Selection.AutoFilter
ActiveCell.Offset(-5, 0).Rows("1:1").EntireRow.Select
ActiveCell.Offset(-5, -9).Range("A1").Activate
Selection.AutoFilter
ActiveCell.Cells.Select
ActiveCell.Activate
Selection.Sort Key1:=ActiveCell.Offset(0, 11).Range("A1"), Order1:= _
xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Selection.Sort Key1:=ActiveCell.Offset(0, 11).Range("A1"), Order1:= _
xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.AutoFilter
Selection.AutoFilter Field:=12, Criteria1:="<2", Operator:=xlAnd
ActiveCell.Offset(155, 0).Rows("1:129").EntireRow.Select
Selection.ClearContents
Selection.AutoFilter Field:=12
ActiveCell.Offset(-155, 0).Rows("1:1").EntireRow.Select
Selection.AutoFilter
ActiveCell.Cells.Select
Selection.Sort Key1:=ActiveCell.Offset(0, 7).Range("A1"), Order1:= _
xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

 

[MrKowz]

Is your entire code based off the position of the selection BEFORE you run the macro, or does the macro select a cell to begin with? It doesnt look like your entire code copied over (also, please out

 tags around your code so it is easier to read and debug)

 

[jindon]

ActiveCell.Offset(-5, 0).Rows("1:1").EntireRow.Select

You don't need the red part.

ActiveCell is a single object and offsetting by -5 and that EntireRow is specified already.