Solved

Managing Range Selection in Excel Macro

Posted on 2013-12-10
6
793 Views
Last Modified: 2013-12-10
I have an Excel workbook with a bunch of tabs that refresh from a connected Access database. The refreshed data fills columns starting with A and then out to the right for between 5 and 12 columns. For a special purpose, I have formulas further out to the right which perform conversions on some of the refreshed data and I want to have a Macro that does a couple of things:

1: Deletes all of the formula rows below the range of the refreshed data. (So, if the refreshed data fills up down to row 10, for example, I want to delete all rows from 11 on down.)

2: "Copy/Paste/Values" of the formula data in on top of the refreshed columns. (So the data calculated by the formulas replaces the data brought in from Access.)

I tried to record a macro to do this, and most of it works OK. My problem is that to identify the first "blank" row in the refreshed data, I have been moving the Excel cursor to the top of one of the refresh columns (one I KNOW has data for all rows) and then using the "End/Down" to get to the bottom populated row. Then, to get to the NEXT row down, I hit the down arrow key, and this is the problem. The Macro does not record "Down One Row." It records the explicit selection of the cell I moved into - e.g. "H11". This means the macro will only work for this specific import and if a new refresh pulls in a different number of rows, the Macro is useless.

How can I replace the explicit selection of the cell with something generic that identifies the "Next Row Down" so that my delete command will remove the desired rows regardless of how the number of rows varies from refresh to refresh?

Thanks.
0
Comment
Question by:Buck_Beasom
[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
  • 4
6 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39708474
You can use the Application.Offset function:

Application.Offset(RowOffset, ColumnOffset).Range(Start:Finish).Select

So to go down one row and select a single cell:

Application.Offset(1,0).Select

To go down one row and across one column:

Application.Offset(1,1).Select

To go down one row and select a range 5 columns wide and 3 rows deep:

Application.Offset(1,0).Range("A1:E3").Select

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39708478
Also, when recording a macro, there is an option to record for Relative cell references rather than Absolute. Activating this would have have used the Offset method for you.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39708491
There are also ways and means of not physically selecting the cell to ascertain the extent of the data but I don't recall the syntax using the "Rows.Count" property. For the first blank row it would be "Rows.Count +1". This also gets round the need for the data in the column to be continuous, ie scenarios with non-continuous data where the End/Down wouldn't work.

Thanks
Rob
0
Technology Partners: 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!

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39708589
What I think you are looking for is;

if your data column is H then

range("H1").end(xldown).offset(1)

will give the first blank row below the data if H1 and H2 are not blank.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39708706
I assume therefore that:

Range("H1048576").end(xlup).offset(1)

Would give same result but allow for scenario where column H may not be continuous.
0
 

Author Closing Comment

by:Buck_Beasom
ID: 39708760
All your comments were outstanding and very helpful.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 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