Solved

VBA Select Range when rows and columns are dynamic

Posted on 2012-03-17
3
285 Views
Last Modified: 2012-06-22
I would like to create a macro that will select the range based on the following:
1) Upper left is determined by wherever the curser is placed when the macro is executed.
2) Lower right is determined by last row with data in COL A and last column with a header.

The macro will be executed on the active worksheet in whatever workbook is open.

For example, this code gives me lots of empty records:     Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Thanks for your help, Experts!
0
Comment
Question by:thutchinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 37732676
Try this.
LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Range(ActiveCell, Cells(LastRow, LastCol)).Select

Open in new window

0
 

Author Comment

by:thutchinson
ID: 37732696
That's fantastic.  Thanks very much.
0
 

Author Closing Comment

by:thutchinson
ID: 37732698
Quick reply, great answer. in a word, "Perfect!"
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question