Auto Select (again) in Macro

Scorpion Steve

Board Regular
Joined
Jun 5, 2011
Messages
220
Guys,
Im getting muddled up!
I had a great solution of which I picked apart and tried to understand so that I could use it else where but failed!
CODE:
Range("A1:F24").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$24"
ActiveCell.FormulaR1C1 = "ax"
Range("J4").Select
End Sub

This was the solution code:
With Range("A2", Range("A2").End(xlDown))
Range("A1").Resize(.Rows.Count).Value = .Value
Range("B1").Resize(.Rows.Count).FillDown
ActiveSheet.PageSetup.PrintArea = Range("A1").CurrentRegion.Address
End With
End Sub

All im needing is have the macro do the follwing:
**Bear in mind, autoselect for me = CRTL+SHFT+RIGHT ARROW, then the same but the DOWN ARROW

Macro activated=
Select cell A1, autoselect data (must be variable)
Print select that area
End

Thanks
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
For instance:

Code:
With Range("A1", Range("A1").End(xlDown).End(xlToRight))
    .Parent.PageSetup.PrintArea = .Address
    .Cells(1,1).Value = "ax"
End With
Although I'm not sure I understand what you want to do with "ax".

PS: As I must have asked you earlier, can you please use
Code:
 tags when you paste a bunch of code here. Thanks.
 

Scorpion Steve

Board Regular
Joined
Jun 5, 2011
Messages
220
Auto Select Failure in a VBA

Hey Wigi,
I have checked out your web page and shall be donating for your efforts!
This code didn't work for me!
I fell I haven't explained correctly.
I have attached a screen print to help explain!

Notes: I need this to be variable
Thanks again
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Hello there

The screenshot did not come true unfortunately.

A clear and concise explanation in words (with the cells in the file that you use) will be enough for me to write code.
 

Scorpion Steve

Board Regular
Joined
Jun 5, 2011
Messages
220
Re: Auto Select in Macro

Hi, Sorry!
The image didn't upload correctly but if you were to right click the logo in my last post (and 'open in new tab')
It will open and all will be clear.
Im sorry, I cannot explain that well.
My English is not that great
or
http://www.freeimagehosting.net/12778
 

Scorpion Steve

Board Regular
Joined
Jun 5, 2011
Messages
220
Complete!
All I have done is named my 1st static cell "Range_start"
the I created a macro and stopped recording straight away (this is because I am needing this process to be attached to a button)
Then rewrote the code with;

Application.Goto reference:="range_start"
Range(ActiveCell, ActiveCell.End(xlDown).Offset(0, 5)).Select
Selection.Copy

The "Offset(0, 5))" part means how many cells down, cell across

Thanks again for all efforts!
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello

It seems that my messages reach you, but they do not come through :)

You can replace the above code with:

Code:
Range("range_start", Range("range_start").End(xlDown).Offset(, 5)).Copy

But following this approach, you avoid selecting ranges: this is what the macro recorder does, but it is not needed. The macro "records" what you do manually, it does not "write" code. While the macro recorder code works, you can improve by cleaning up that code and removing .Select, Selection., .Activate, scrolling and a bunch of others.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,547
Messages
5,596,782
Members
414,101
Latest member
ExcelBasicBro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top