Mark a range from activecell

C

Ctech

Hi Im trying to select a range with a macro...

I would like to select the range from the active cell and X rows down.
How can I do this?

My code:

Myrows = Selection.rows.count
MST = Activecell.address

Range(& ActiveCell & " : " & Myrows )

However this gives me, i.e. B3:53

How can I get the Column letter before Myrows?
 
L

Leith Ross

Hello Ctech,

Use ActiveCell.Offset(<rows>, <cols>)

Myrows = Selection.rows.count

'Zero means stay in the same Row or Column
MST = ActiveCell.Offset(Myrows, 0).Address

Sincerely,
Leith Ros
 
L

Leith Ross

Hello Ctech,

Use ActiveCell.Offset(<rows>, <cols>)

Myrows = Selection.rows.count

'Zero means stay in the same Row or Column
MST = ActiveCell.Offset(Myrows, 0).Address

Sincerely,
Leith Ros
 
C

Ctech

This is the code I've written now..

However it still does not work.


' Mark formulas and Copy them

MST1 = ActiveCell.Address

MST = ActiveCell.Offset(0, 4).Address

Range(MST1 & ":" & MST).Copy
 
C

Ctech

This is my whole macro so far...


Private Sub cmd2_Click()
Dim Mrange
Dim Mrows As Long
Dim Mcolumns As Long
Dim Mcell
Dim McolumnC
Dim MrowC
Dim MST
Dim MST1


Dim McolumnC2
Dim MrowC2


'Picks up the range from the form

Mrange = ChangeToDateFormat.RefEdit1

Range(Mrange).Select

Mcell = ActiveCell.Address

' Counts the Rows and Columns of the selection

Mrows = Selection.Rows.Count
Mcolumns = Selection.Columns.Count

' Adds Columns to work with

Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight

' Adds the forumals in the added columns
Range(Mcell).Select
McolumnC = Range(Mcell).Column
MrowC = Range(Mcell).Row


ActiveCell.FormulaR1C1 = "=RC[1]&""/""&RC[2]&""/""&RC[3]"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[3],2)"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MID(RC[2],5,2)"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[1],4)"

' Mark formulas and Copy them
MST1 = ActiveCell.Address
MST = ActiveCell.Offset(0, 4).Address

Range("" & MST1 & ":" & MST).Copy

'Zero means stay in the same Row or Column

MST = ActiveCell.Offset(myRows, 4).Address

Range("" & MST1 & ":" & MST).Paste

' Copy and paste special values, Result

MST = ActiveCell.Offset(Mrows, 0).Address

Range("" & MST1 & ":" & MST).Copy
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False

' Delete the old and unneeded columns

MST1 = ActiveCell.Offset(0, 1).Address
MST = ActiveCell.Offset(Mrows, 4).Address

Range("" & MST1 & ":" & MST).Select

Selection.Delete Shift:=xlToLeft
End Su
 
C

Ctech

I found the solution(s) to my problem:


Here is my final code:



Private Sub cmd1_Click()
Unload ChangeToDateFormat
End Sub

Private Sub cmd2_Click()
Dim Mrange
Dim Mrows As Long
Dim Mcolumns As Long
Dim Mcell
Dim McolumnC
Dim MrowC
Dim MST
Dim MST1


Dim McolumnC2
Dim MrowC2


'Picks up the range from the form

Mrange = ChangeToDateFormat.RefEdit1

Range(Mrange).Select

Mcell = ActiveCell.Address

' Counts the Rows and Columns of the selection

Mrows = Selection.Rows.Count
Mcolumns = Selection.Columns.Count

' Adds Columns to work with

Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight

' Adds the forumals in the added columns
Range(Mcell).Select
McolumnC = Range(Mcell).Column
MrowC = Range(Mcell).Row

ActiveCell.FormulaR1C1 = "=value(RC[1])"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[1]&""/""&RC[2]&""/""&RC[3]"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[3],2)"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MID(RC[2],5,2)"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[1],4)"

' Mark formulas and Copy them
ActiveCell.Offset(0, -4).Select
MST1 = ActiveCell.Address
MST = ActiveCell.Offset(0, 4).Address

Range("" & MST1 & ":" & MST).Select
Range(MST).Activate
Selection.Copy

'Zero means stay in the same Row or Column
Range(MST1).Select
Mrows = Mrows - 1

MST = ActiveCell.Offset(Mrows, 4).Address

Range("" & MST1 & ":" & MST).Select
ActiveSheet.Paste

' Copy and paste special values, Result

MST = ActiveCell.Offset(Mrows, 0).Address

Range("" & MST1 & ":" & MST).Copy
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False

' Delete the old and unneeded columns

MST1 = ActiveCell.Offset(0, 1).Address
MST = ActiveCell.Offset(Mrows, 5).Address

Range("" & MST1 & ":" & MST).Select

Selection.Delete Shift:=xlToLeft


' Change it to Date Format
MST1 = Range(MST1).Offset(0, -1).Address
MST = ActiveCell.Offset(Mrows, 0).Address
Range("" & MST1 & ":" & MST).Select

Selection.NumberFormat = "d-mmm-yy"

' Mark first cell in range

Range(MST1).Select

Unload ChangeToDateFormat
End Su
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select range from ActiveCell do to Lastcell 5
Deleting rows that contain numbers (a range) in column D 7
active workbook/sheet 3
Another Try - Calculate from ActiveCell 9
After copy of row, cannot paste to activated sheet 5
Create Detail Sheets from Pivot Table 1
Select range from ActiveCell do to Lastcell in ColumnA 3
Pass activecell(.address?) as a range in a function parameter? 3

Top