Selection.End(xldown).Select variable number of times

[Will85]

In column A, I have strategically placed the value of x in specific rows.

I use a macro that goes to column A and then executes selection.end(xldown).Select a certain number of times depending on which row I want it to land on.

For example if I want it to select the third row with an x I use:

Range("A1").Select
selection.end(xldown).Select
selection.end(xldown).Select
selection.end(xldown).Select


I would prefer to be able to tell it to go down a specific # of times based on the value in a cell on another sheet.

Additionally, I need it to look up this value, instead of pointing to a specific cell reference.

For example, Id want to index match the value based on an account number, the account number its looking up can be hardcoded inside the macro itself.

Sheet2 has account numbers in column A and the value I am seeking in column B.

 

[Will85]

What I am using now:

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
Shp.TopLeftCell.Offset(-1).EntireRow.Copy


Selection.Insert shift:=xlDown
Selection.Locked = False
Selection.FormulaHidden = False
Selection.RowHeight = 50
End Sub

If the button is in row 25, then the active cell needs to be A25 in order to copy correctly.

In the properties, if you set "Move with cells" you could use something like

Sub Will85()
   Dim Shp As Shape
   Set Shp = ActiveSheet.Shapes(Application.Caller)
      Shp.TopLeftCell.Offset(-1).EntireRow.Copy
End Sub

 

[Fluff]

How about

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Insert xlDown
   .Locked = False
   .FormulaHidden = False
   .RowHeight = 50
End With
End Sub

 

[Will85]

Its almost working perfect! It is copying the row above where the button is perfect, no matter where the active cell is, except that its not changing the height of the copied row to 50, its changing the height of the row that the button is in to 50.

The row above the button, which is being copied and inserted, is row height 0. I want to copy this row, insert it above, and increase the height to 50.

Row 1 is blank, row 2 is the row to be copied and is size 0, row 3 has the button.

After clicking the button the result would be, row 1 would be blank, row 2 would now be a duplication of the copied row at size 50, row 3 is the "static" row to be copied, and now row 4 has the button.

Thoughts?

thank you for your continued help.

How about

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Insert xlDown
   .Locked = False
   .FormulaHidden = False
   .RowHeight = 50
End With
End Sub

 

[Fluff]

How about

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Insert xlDown
   .Locked = False
   .FormulaHidden = False
   .Offset(-2).RowHeight = 50
End With
End Sub

 

[Will85]

Perfect, thank you!

How about

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Insert xlDown
   .Locked = False
   .FormulaHidden = False
   .Offset(-2).RowHeight = 50
End With
End Sub

 

[Fluff]

You're welcome & thanks for the feedback

 

[Will85]

I just noticed that my sums are not working correctly now.

Row 1 is Blank
Row 2 is the static row to be copied size 0
Row 3 has the button, and also has sums in further columns that sum 1:2

When I hit the button I get
Row 1 is Blank
Row 2 now a duplicate and size 50
Row 3 is the static row to be copied size 0
Row 4 has the button, but instead of my new sums being 1:3, it stuck at 1:2.

You're welcome & thanks for the feedback

 

[Fluff]

Without being able to see the formulae, I wouldn't know.

 

[Will85]

column A is blank
B2="copy me"
C2=1
B3=button for macro
C3=sum(C1:C2) evaluates to 1

push button

column A is blank
B2="copy me"
C2=1
B3="copy me"
C3=1
B4=button for macro
C4=sum(C1:C2) evaluates to 1. I want it to be sum(C1:C3) evaluate to 2. The sum range should grow as new rows are inserted...

Without being able to see the formulae, I wouldn't know.

 

[Fluff]

Try

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Insert xlDown
   .Locked = False
   .FormulaHidden = False
   .Offset(-2).RowHeight = 50
   Range("C" & .Row).Replace ":C*", ":C" & .Row - 1 & ")", xlPart, , False, , False, False
End With
End Sub