Simple Excel VBA Range Reference Question

[marcus101]

Hello:

I'm experimenting with some Excel 2003 automation using variable ranges and selections from two different workbooks.

One is a data file with about 25,000 rows, the other is a basic output file that is acting as a reporting template.

Here's what I'd like it to do:

I'm currently creating a procedure that uses existing values on the reporting template to create a set of filtering criteria I can use against the data file.

This part works.

I then run the criteria against the data using an Advanced Filter.

This part works.

In the filtered data, I wish to generate sub-totals for my results. I don't need unique ones.

This part works, sort of.

I then need to re-run this filter. I use the Show All command and then re-set the filter criteria.

This part works, sort of.

When I re-run the advanced filter with the new criteria, I get ADDITIONAL rows at the bottom. For some reason, when I do this manually, this DOESN'T happen.

Here's where the selection part comes in:

In order for me to deal with this problem of having extra sub-totaling rows (plus rows that might be in variable positions for the data returned by the criteria), I've written some basic code that grabs the last cell (bottom right, using CTRL-END) location in order to determine how many rows I need to offset shift the selection to grab the data I need for the subtotal.

Here's my concern:

If I select three columns as a range, ie: (A3000:C3000), will ALL 3 COLUMNS BE SELECTED if I OFFSET?

I'm guessing either yes or no to this.

I'm almost afraid to test it, because if the answer to this is NO, then the only way I can see getting at the individual cells I need is to manually select ONE CELL AT A TIME for many hundreds, if not thousands of iterations.

The other idea I had was to generate a range value as a String Variable. This works, but when I pass the string variable into a Range Selection reference, it doesn't work.

Here's an example:

' Criteria/Filtering Processing code...

myRange = "Q1500:S1500"  ' (generated as String)
myRangeB = "Q1500:S1500" ' (generated as Range)

Range(myRange).Select
Range(myRangeB).Select

Both of these examples trigger an error #1044.

I've tried creating Range objects and passing the string reference into that and that doesn't work either.

I've looked at thread707-1228184, and this seems to provide some ways of manually working around the problem.

Maybe I can just create a selected range a piece at a time by using the "&" operator along with a partially fixed range reference, but in my trials, this doesn't seem to work for some reason, and I get an error #1044 again.

My head is so much into code right now, maybe I'm missing something obvious?

Fundamentally, all I want to do is:

a) Extend a multiple-cell selection using offset, so that I don't lose my multiple cells. If all I get in my selection after the offset is a single cell, then that totally does not work for me.

OR

b) The option of generating the range reference I need using manual code processing, but then selecting the multi-cell reference by passing a variable value into it using Range.Select.

Any thoughts on this one welcome.

Thanks,

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[SkipVought]

Hi,

First, I am confused by your code example: a distinction without a difference. What does [tt]myRangeB = "Q1500:S1500" ' (generated as Range)[/tt] mean? myRangeB is a STRING. If you were to fully quallify the RANGE Select statements, they WOULD work.

First, you don't have to loos anything. Set you range to a range varaible. Then extend off that if you wish. I don't understand your predicament. Maybe a small concrete example would help me understand.

Skip,

_[red][/red]