Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drag cell from A1 : A (Value here)
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drag cell from A1 : A (Value here)
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. Ed wrote: 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! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drag cell from A1 : A (Value here)
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 "Dave Peterson" wrote: 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. Ed wrote: 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! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drag cell from A1 : A (Value here)
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. Ed wrote: 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 "Dave Peterson" wrote: 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. Ed wrote: 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! -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drag cell from A1 : A (Value here)
Thankyou very much Dave!
"Dave Peterson" wrote: 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. Ed wrote: 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 "Dave Peterson" wrote: 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. Ed wrote: 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! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
how can I drag formulas with other cell references | Excel Discussion (Misc queries) | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |