Drag cell from A1 : A (Value here)

E

Ed

Hello, suppose I have in cell B1 with the value of "x" can anyone suggest me
a macro which would drag cell A1 (A1 has a formula) until A"x"?

I need a macro to drag down formulas, but the problem is have is the
following:

I've recorded a Macro, and for example the moment when I recorded it, I had
at that time 30 rows, so it records the following marcro:

Private Sub CommandButton1_Click()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1:A30").Select
Selection.FillDown
End Sub

And if next time I have just 15 it drags until 30 and so on, how can I
manage to have the value in this case of "30" to be taken from a value in
cell B1??

thanks!
 
D

Dave Peterson

Maybe...

Private Sub CommandButton1_Click()
Dim LastRow as long

With me
LastRow = .range("B1").value
Range("a1:A" & LastRow).filldown
End with
End Sub

Not too much validation, though.
 
E

Ed

Hello Dave and thanks for your answer, another doubt came into my mind, maybe
you can help me with it, how can I do the same but for Columns instead of
rows?? And with a keyboard shortcut rather than a button?

thanks
 
D

Dave Peterson

Put this in a general module:

Option Explicit
Sub testme()
Dim LastCol As Long
With ActiveSheet
LastCol = .Range("a2").Value
Range("a1", .Cells(1, LastCol)).FillRight
End With
End Sub

Then back to excel
tools|macro|macros...
select the macro
then click options and assign it the shortcut combo of your choice.
 
E

Ed

Thankyou very much Dave!

Dave Peterson said:
Put this in a general module:

Option Explicit
Sub testme()
Dim LastCol As Long
With ActiveSheet
LastCol = .Range("a2").Value
Range("a1", .Cells(1, LastCol)).FillRight
End With
End Sub

Then back to excel
tools|macro|macros...
select the macro
then click options and assign it the shortcut combo of your choice.
Click to expand...
 
Top