I'm trying to develop some macros. I'm not that versed
at it yet. Here is part of something:

Dim iLastCol As Long

With Worksheets("2006 Realized Gains")
Cells.Select
Selection.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Application.Goto Reference:= _
"INDEX(R1:R65536,COUNTA(C[1])-1,30)"

See that "30" in the last line? That's Column 30 ("AD"). I
tried to use my "iLastCol" variable instead, but could
not. I tried all sorts of permutations; still no luck.
What's the trick?

Seconds question: How can I save the Application.Goto Reference
for repeat use without inserting that statement again each time?

E.g., later I will want to select the row below that
row. How would I do that?

Thanks for any help.
Dallman Ross

Dim iLastCol As Long
dim iLastRow as long

With Worksheets("2006 Realized Gains")
'you have to select the sheet before you can select a range
'but you don't have to select either to get your code to work
'.select
'.Cells.Select
.cells.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
iLastRow = .cells(.rows.count,"A").end(xlup).row

application.goto _
reference:=.cells(ilastrow + 1, ilastcol) ',scroll:=true

end with

I used column A to find the nextrow (iLastRow + 1) to go to.

I don't understand the second question.

Dallman Ross wrote:

I'm trying to develop some macros. I'm not that versed
at it yet. Here is part of something:

Dim iLastCol As Long

With Worksheets("2006 Realized Gains")
Cells.Select
Selection.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Application.Goto Reference:= _
"INDEX(R1:R65536,COUNTA(C[1])-1,30)"

See that "30" in the last line? That's Column 30 ("AD"). I
tried to use my "iLastCol" variable instead, but could
not. I tried all sorts of permutations; still no luck.
What's the trick?

Seconds question: How can I save the Application.Goto Reference
for repeat use without inserting that statement again each time?

E.g., later I will want to select the row below that
row. How would I do that?

Thanks for any help.
Dallman Ross

--

Dave Peterson

In , Dave Peterson
helped me out by fixing up my code some:

Dim iLastCol As Long
dim iLastRow as long

With Worksheets("2006 Realized Gains")
'you have to select the sheet before you can select a range
'but you don't have to select either to get your code to work
'.select
'.Cells.Select
.cells.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
iLastRow = .cells(.rows.count,"A").end(xlup).row

application.goto _
reference:=.cells(ilastrow + 1, ilastcol) ',scroll:=true

end with

Thanks, Dave! That's much better.
A couple more questions: I used "Long" as the var type, and I
chose that because you'd done so earlier (in another thread where
you helped me). But why are we doing that? Why not Integer type?

I used column A to find the nextrow (iLastRow + 1) to go to.

Okay. I don't know if I need the application.goto stuff or not,
but after lots of experimentation recording macros and inspecting
the code and trying to bend it to my needs, I'd come up with that.
Actually, I took the "+ 1" back out, because my next line
in my macros is:

Range(Selection, "A1").Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

Maybe there's a better way to invoke that range for the sort.

I don't understand the second question.

The second question was how I can keep the value of the
goto reference so I don't have to repeat

application.goto _
reference:=.cells(ilastrow, ilastcol)

a bunch of times when I do other things later in the macro.

Probably if I knew more I wouldn't be using goto anyway,
so the question wouldn't be needed. But I don't know
other ways to set ranges for actions yet.

The next thing I want to do, for example, is move the row
defined as "iLastRow + 1" down X number of lines, where
X is determied by the number of rows in another sheet that
I've just refreshed data for. (The row I'm moving is a
row of totals and other calculated fields.)

(Then I'll want to fill the rows I've just inserted down to
the new location of my bottom, totals, row.)

================================================== ============
Dallman Ross wrote:

I'm trying to develop some macros. I'm not that versed
at it yet. Here is part of something:

Dim iLastCol As Long

With Worksheets("2006 Realized Gains")
Cells.Select
Selection.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Application.Goto Reference:= _
"INDEX(R1:R65536,COUNTA(C[1])-1,30)"

See that "30" in the last line? That's Column 30 ("AD"). I
tried to use my "iLastCol" variable instead, but could
not. I tried all sorts of permutations; still no luck.
What's the trick?

Seconds question: How can I save the Application.Goto Reference
for repeat use without inserting that statement again each time?

E.g., later I will want to select the row below that
row. How would I do that?

Thanks for any help.
Dallman Ross

Longs can go from -2,147,483,648 to 2,147,483,647.
Integers can go from -32,768 to 32,767

Since there are 64k rows in a worksheet, using Integer could cause trouble if
you exceed 32,767 rows. And on top of that (from what I've read), modern
computers will convert Integers to Longs before they work with them. So why
waste their time!

Instead of selecting the cells (using .select or application.goto), you can work
directly with the range.

For instance:

dim iLastCol as long
dim iLastRow as long
dim myRng as range

with worksheets("sheet9999")
ilastcol = .cells(1,.columns.count).end(xltoleft).column
ilastrow = .cells(.rows.count,"A").end(xlup).row

set myrng = .range("a1",.cells(ilastrow,ilastcol)
end with

Then you can do what you want to that range

with myrng
.sort key1:=.columns(2), order1:=xlascending, header:=xlyes
end with

..columns(2) belongs to myrng. It's the second column within that range--maybe
not the second column in the worksheet (in this sample, it is, but it doesn't
have to be).

I'm not sure I'd keep track of any variable (sometimes, I do, though).

I like to just come back and figure it out again:

ilastrow = .cells(.rows.count,"A").end(xlup).row

If I've done something that increase/decreased the number of rows in that
worksheet.


Dallman Ross wrote:

In , Dave Peterson
helped me out by fixing up my code some:

Dim iLastCol As Long
dim iLastRow as long

With Worksheets("2006 Realized Gains")
'you have to select the sheet before you can select a range
'but you don't have to select either to get your code to work
'.select
'.Cells.Select
.cells.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
iLastRow = .cells(.rows.count,"A").end(xlup).row

application.goto _
reference:=.cells(ilastrow + 1, ilastcol) ',scroll:=true

end with

Thanks, Dave! That's much better.
A couple more questions: I used "Long" as the var type, and I
chose that because you'd done so earlier (in another thread where
you helped me). But why are we doing that? Why not Integer type?

I used column A to find the nextrow (iLastRow + 1) to go to.

Okay. I don't know if I need the application.goto stuff or not,
but after lots of experimentation recording macros and inspecting
the code and trying to bend it to my needs, I'd come up with that.
Actually, I took the "+ 1" back out, because my next line
in my macros is:

Range(Selection, "A1").Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

Maybe there's a better way to invoke that range for the sort.

I don't understand the second question.

The second question was how I can keep the value of the
goto reference so I don't have to repeat

application.goto _
reference:=.cells(ilastrow, ilastcol)

a bunch of times when I do other things later in the macro.

Probably if I knew more I wouldn't be using goto anyway,
so the question wouldn't be needed. But I don't know
other ways to set ranges for actions yet.

The next thing I want to do, for example, is move the row
defined as "iLastRow + 1" down X number of lines, where
X is determied by the number of rows in another sheet that
I've just refreshed data for. (The row I'm moving is a
row of totals and other calculated fields.)

(Then I'll want to fill the rows I've just inserted down to
the new location of my bottom, totals, row.)

================================================== ============
Dallman Ross wrote:

I'm trying to develop some macros. I'm not that versed
at it yet. Here is part of something:

Dim iLastCol As Long

With Worksheets("2006 Realized Gains")
Cells.Select
Selection.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Application.Goto Reference:= _
"INDEX(R1:R65536,COUNTA(C[1])-1,30)"

See that "30" in the last line? That's Column 30 ("AD"). I
tried to use my "iLastCol" variable instead, but could
not. I tried all sorts of permutations; still no luck.
What's the trick?

Seconds question: How can I save the Application.Goto Reference
for repeat use without inserting that statement again each time?

E.g., later I will want to select the row below that
row. How would I do that?

Thanks for any help.
Dallman Ross


--

Dave Peterson

In , Dave Peterson
spake thusly:

Longs can go from -2,147,483,648 to 2,147,483,647.
Integers can go from -32,768 to 32,767

This is good stuff you're feeding me. Tks. :-)

Since there are 64k rows in a worksheet, using Integer could
cause trouble if you exceed 32,767 rows. And on top of that
(from what I've read), modern computers will convert Integers to
Longs before they work with them. So why waste their time!

Okay, I see.


Instead of selecting the cells (using .select or
application.goto), you can work directly with the range.

For instance:

dim iLastCol as long
dim iLastRow as long
dim myRng as range

Oh!


with worksheets("sheet9999")
ilastcol = .cells(1,.columns.count).end(xltoleft).column
ilastrow = .cells(.rows.count,"A").end(xlup).row

set myrng = .range("a1",.cells(ilastrow,ilastcol)
end with

Yup, that rocks! (Needed a matching closed paren at the end of the
"set myrng" line, but I figured that out quickly.)

I notice it the code doesn't give me a blatant error if I
don't use "set" -- though I didn't try to run it without.
But how do I know when to use set and when not to?


I'm also getting confused about when to invoke a "with" and when
to end it. E.g., in my macro I unhide columns before the
sort (to make sure Col. A. was sorted, as it is normally
hidden). Is that inside the 'With worksheets("name")' ?


Then you can do what you want to that range

with myrng
.sort key1:=.columns(2), order1:=xlascending, header:=xlyes
end with

Good. "Key1:=.rows(2)", of course, but I know you're typing fast. :-)
That puzzled me for a moment, but I decided to risk trying to run it
with ".columns(2)" and guessed it wouldn't do anything horrible to
my data, which it didn't. But that did tell me I wanted .rows, since
nothing much happened with .columns, and they didn't make sense to
me there anyway. :-) :-)

I generally back up my stuff before changes, of course, but still . . .

.columns(2) belongs to myrng. It's the second column within that
range--maybe not the second column in the worksheet (in this
sample, it is, but it doesn't have to be).

I'm not sure I'd keep track of any variable (sometimes, I do, though).

Well, if you know you're through with one, do you bother to free
up env space?

I like to just come back and figure it out again:

ilastrow = .cells(.rows.count,"A").end(xlup).row

If I've done something that increase/decreased the number of rows in that
worksheet.

Okay.

In any case, I'm working fine so far. Now I refresh the data source
on another sheet in this workbook. (My macro does that, but it
stops and asks me to confirm the name. I don't know how to get it
not to ask me. So that's one more question.)

The next thing that happens is, the sheet we've been working on
spits at me in the totals row over what is now a circular reference.
That's because there are named ranges in use referring to the
data sheet I've just refreshed and made longer, so the totals
row now finds itself in the middle of the data it's totalling. Oops.

So I need to tell the macro to shut up already about circular refs,
because I'm about to move the totals row to the new end and fill down
the new cells I'll insert. More coding help will be very much
appreciated!

================================================== ==================
Dallman Ross wrote:

In , Dave Peterson
helped me out by fixing up my code some:

Dim iLastCol As Long
dim iLastRow as long

With Worksheets("2006 Realized Gains")
'you have to select the sheet before you can select a range
'but you don't have to select either to get your code to work
'.select
'.Cells.Select
.cells.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
iLastRow = .cells(.rows.count,"A").end(xlup).row

application.goto _
reference:=.cells(ilastrow + 1, ilastcol) ',scroll:=true

end with

Thanks, Dave! That's much better.
A couple more questions: I used "Long" as the var type, and I
chose that because you'd done so earlier (in another thread where
you helped me). But why are we doing that? Why not Integer type?

I used column A to find the nextrow (iLastRow + 1) to go to.

Okay. I don't know if I need the application.goto stuff or not,
but after lots of experimentation recording macros and inspecting
the code and trying to bend it to my needs, I'd come up with that.
Actually, I took the "+ 1" back out, because my next line
in my macros is:

Range(Selection, "A1").Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

Maybe there's a better way to invoke that range for the sort.

I don't understand the second question.

The second question was how I can keep the value of the
goto reference so I don't have to repeat

application.goto _
reference:=.cells(ilastrow, ilastcol)

a bunch of times when I do other things later in the macro.

Probably if I knew more I wouldn't be using goto anyway,
so the question wouldn't be needed. But I don't know
other ways to set ranges for actions yet.

The next thing I want to do, for example, is move the row
defined as "iLastRow + 1" down X number of lines, where
X is determied by the number of rows in another sheet that
I've just refreshed data for. (The row I'm moving is a
row of totals and other calculated fields.)

(Then I'll want to fill the rows I've just inserted down to
the new location of my bottom, totals, row.)

================================================== ============
Dallman Ross wrote:

I'm trying to develop some macros. I'm not that versed
at it yet. Here is part of something:

Dim iLastCol As Long

With Worksheets("2006 Realized Gains")
Cells.Select
Selection.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Application.Goto Reference:= _
"INDEX(R1:R65536,COUNTA(C[1])-1,30)"

See that "30" in the last line? That's Column 30 ("AD"). I
tried to use my "iLastCol" variable instead, but could
not. I tried all sorts of permutations; still no luck.
What's the trick?

Seconds question: How can I save the Application.Goto Reference
for repeat use without inserting that statement again each time?

E.g., later I will want to select the row below that
row. How would I do that?

Thanks for any help.
Dallman Ross

Sorry about missing the closing paren.

But you use Set when you want to make an assignment to an object variable.

Dim Wks as worksheet
set wks = worksheets(...)

dim wkbk as workbook
set wkbk = workbooks(...)

dim pt as pivottable
set pt = activesheet.pivottables(...)

These objects have lots of properties that can be addressed.
wks.name, wks.range(...), wks.protectcontents and lots more stuff.

If the variable represents something simple (no properties), then you don't use
the Set

Dim myVal as long or string or boolean or...

myval = 1234
myval = "qwer"
myval = false

=====================
In my sort sample, I did want key1:=.columns(2). This meant that my column to
sort by was column 2 of that range (the dot (.) said that columns(2) belonged to
the previous With statement. It wasn't an error.

=====================

I like typing:

with worksheets("sheet999")
set myRng = .range(.cells(1,1),.cells(ilastrow, ilastcol))
end with

But if you like you can qualify each range reference:

set myrng = worksheets("sheet999").range(worksheets("sheet999" ).cells(1,1), _
worksheets("sheet999").cells(ilastrow, ilastcol))

(watch out for typos. I didn't count my ()'s.

I think it makes the code easier to read and understand (and type!).

=========
If I've ever bothered freeing any variable space, it was a mistake! Excel/VBA
will release any memory used when the procedure ends running. (There are some
bugs that cause a memory leak, but I don't worry about them either. You can
google "memory leaks" if you're really interested.

And if you automate excel from another application, you'll want to release your
object variables:

set wkbk = nothing
set wks = nothing
set xlapp = nothing

But that's a different subject.

==========

I'm not sure what you're refreshing that causes the warning, but lots of times,
you can suppress warnings by:

application.displayalerts = false
'your code that causes the warning message here
application.displayalerts = true

Another option would be to remove your subtotals, add your rows, and reinsert
the subtotal rows.

Or insert your new data above the subtotal rows.

David McRitchie has some notes about not having to adjust formulas when
additional rows are inserted:

http://www.mvps.org/dmcritchie/excel...row.htm#offset

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

Longs can go from -2,147,483,648 to 2,147,483,647.
Integers can go from -32,768 to 32,767

This is good stuff you're feeding me. Tks. :-)

Since there are 64k rows in a worksheet, using Integer could
cause trouble if you exceed 32,767 rows. And on top of that
(from what I've read), modern computers will convert Integers to
Longs before they work with them. So why waste their time!

Okay, I see.

Instead of selecting the cells (using .select or
application.goto), you can work directly with the range.

For instance:

dim iLastCol as long
dim iLastRow as long
dim myRng as range

Oh!


with worksheets("sheet9999")
ilastcol = .cells(1,.columns.count).end(xltoleft).column
ilastrow = .cells(.rows.count,"A").end(xlup).row

set myrng = .range("a1",.cells(ilastrow,ilastcol)
end with

Yup, that rocks! (Needed a matching closed paren at the end of the
"set myrng" line, but I figured that out quickly.)

I notice it the code doesn't give me a blatant error if I
don't use "set" -- though I didn't try to run it without.
But how do I know when to use set and when not to?

I'm also getting confused about when to invoke a "with" and when
to end it. E.g., in my macro I unhide columns before the
sort (to make sure Col. A. was sorted, as it is normally
hidden). Is that inside the 'With worksheets("name")' ?

Then you can do what you want to that range

with myrng
.sort key1:=.columns(2), order1:=xlascending, header:=xlyes
end with

Good. "Key1:=.rows(2)", of course, but I know you're typing fast. :-)
That puzzled me for a moment, but I decided to risk trying to run it
with ".columns(2)" and guessed it wouldn't do anything horrible to
my data, which it didn't. But that did tell me I wanted .rows, since
nothing much happened with .columns, and they didn't make sense to
me there anyway. :-) :-)

I generally back up my stuff before changes, of course, but still . . .

.columns(2) belongs to myrng. It's the second column within that
range--maybe not the second column in the worksheet (in this
sample, it is, but it doesn't have to be).

I'm not sure I'd keep track of any variable (sometimes, I do, though).

Well, if you know you're through with one, do you bother to free
up env space?

I like to just come back and figure it out again:

ilastrow = .cells(.rows.count,"A").end(xlup).row

If I've done something that increase/decreased the number of rows in that
worksheet.

Okay.

In any case, I'm working fine so far. Now I refresh the data source
on another sheet in this workbook. (My macro does that, but it
stops and asks me to confirm the name. I don't know how to get it
not to ask me. So that's one more question.)

The next thing that happens is, the sheet we've been working on
spits at me in the totals row over what is now a circular reference.
That's because there are named ranges in use referring to the
data sheet I've just refreshed and made longer, so the totals
row now finds itself in the middle of the data it's totalling. Oops.

So I need to tell the macro to shut up already about circular refs,
because I'm about to move the totals row to the new end and fill down
the new cells I'll insert. More coding help will be very much
appreciated!

================================================== ==================
Dallman Ross wrote:

In , Dave Peterson
helped me out by fixing up my code some:

Dim iLastCol As Long
dim iLastRow as long

With Worksheets("2006 Realized Gains")
'you have to select the sheet before you can select a range
'but you don't have to select either to get your code to work
'.select
'.Cells.Select
.cells.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
iLastRow = .cells(.rows.count,"A").end(xlup).row

application.goto _
reference:=.cells(ilastrow + 1, ilastcol) ',scroll:=true

end with

Thanks, Dave! That's much better.
A couple more questions: I used "Long" as the var type, and I
chose that because you'd done so earlier (in another thread where
you helped me). But why are we doing that? Why not Integer type?

I used column A to find the nextrow (iLastRow + 1) to go to.

Okay. I don't know if I need the application.goto stuff or not,
but after lots of experimentation recording macros and inspecting
the code and trying to bend it to my needs, I'd come up with that.
Actually, I took the "+ 1" back out, because my next line
in my macros is:

Range(Selection, "A1").Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

Maybe there's a better way to invoke that range for the sort.

I don't understand the second question.

The second question was how I can keep the value of the
goto reference so I don't have to repeat

application.goto _
reference:=.cells(ilastrow, ilastcol)

a bunch of times when I do other things later in the macro.

Probably if I knew more I wouldn't be using goto anyway,
so the question wouldn't be needed. But I don't know
other ways to set ranges for actions yet.

The next thing I want to do, for example, is move the row
defined as "iLastRow + 1" down X number of lines, where
X is determied by the number of rows in another sheet that
I've just refreshed data for. (The row I'm moving is a
row of totals and other calculated fields.)

(Then I'll want to fill the rows I've just inserted down to
the new location of my bottom, totals, row.)

================================================== ============
Dallman Ross wrote:

I'm trying to develop some macros. I'm not that versed
at it yet. Here is part of something:

Dim iLastCol As Long

With Worksheets("2006 Realized Gains")
Cells.Select
Selection.EntireColumn.Hidden = False
.AutoFilterMode = False

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Application.Goto Reference:= _
"INDEX(R1:R65536,COUNTA(C[1])-1,30)"

See that "30" in the last line? That's Column 30 ("AD"). I
tried to use my "iLastCol" variable instead, but could
not. I tried all sorts of permutations; still no luck.
What's the trick?

Seconds question: How can I save the Application.Goto Reference
for repeat use without inserting that statement again each time?

E.g., later I will want to select the row below that
row. How would I do that?

Thanks for any help.
Dallman Ross



--

Dave Peterson

In , Dave Peterson
spake thusly:

But you use Set when you want to make an assignment to an object
variable.

Dim Wks as worksheet
set wks = worksheets(...)

Ah! Gotcha, thanks.

=====================
In my sort sample, I did want key1:=.columns(2). This meant that
my column to sort by was column 2 of that range (the dot (.) said
that columns(2) belonged to the previous With statement. It
wasn't an error.

Okay. I'm not yet clear on something about it. I guess it's because
I was converting to your syntax from my

Range(Selection, "A1").Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

So I'm sorting based on Column 1. Okay, I'll change it to:

With myRng
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With

Yes, I just tried it, and that works fine. Sorry for being dense!


=====================
I like typing:

with worksheets("sheet999")
set myRng = .range(.cells(1,1),.cells(ilastrow, ilastcol))
end with

But if you like you can qualify each range reference:

set myrng = worksheets("sheet999").range(worksheets("sheet999" ).cells(1,1), _
worksheets("sheet999").cells(ilastrow, ilastcol))


Okay, I see what you like about it. I agree that it's cleaner.

==========
I'm not sure what you're refreshing that causes the warning, but
lots of times, you can suppress warnings by:

application.displayalerts = false
'your code that causes the warning message here
application.displayalerts = true

I'll see how that works. I'm painstakingly building this macro
line-by-line, and I'm not quite there yet. I had a macro earlier
that worked, but I've completely changed my sheet around since
then -- and that macro was not at all optimal, anyway.

Another option would be to remove your subtotals, add your rows,
and reinsert the subtotal rows.

Well, it's formatted a certain way and has some complex calculated
fields, not just totals, so I'd rather not use that approach. I
also could temporarily move it to Row 1, then move it back. If I
do, I think I'll want to turn off the display refresh during that
part of the macro, because that will grate on my sensibilities for
some reason I can't quite put into words. I suppose the reason
is that I'm a purist at heart, but I know too little about what
I'm doing with VBA to be able to live up to my high demands
of myself for creating robust and reusable code.

Or insert your new data above the subtotal rows.

The problem is, I don't know how long the new data is until I refresh
that sheet's data source. The source is a CSV file, so I can't
use fancy VBA code to inspect it first, either.

David McRitchie has some notes about not having to adjust
formulas when additional rows are inserted:

http://www.mvps.org/dmcritchie/excel...row.htm#offset

Bookmarked! Thanks again.

Dallman Ross

[Prior stuff deleted]

I've used this technique when I import files.

I get the data and put it in row 3. I add headers to row 2. I add autofilters
to that range (A2:X9999). But I put all my subtotals in row 1. (and set rows
to repeat at top to 1:2 and freeze panes so that rows 1 and 2 are always
visible).

I find it much easier and even more useful to have the subtotals at the top. If
I use =subtotal()'s in my formulas, they react to the changes in my
autofiltering. And I don't have to scroll through the worksheet to find those
numbers.

Another approach.

Drop the subtotals completely and use data|pivottable to create a nice summary
table. (Or use both the subtotals at the top and pivottables.)

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

But you use Set when you want to make an assignment to an object
variable.

Dim Wks as worksheet
set wks = worksheets(...)

Ah! Gotcha, thanks.

=====================
In my sort sample, I did want key1:=.columns(2). This meant that
my column to sort by was column 2 of that range (the dot (.) said
that columns(2) belonged to the previous With statement. It
wasn't an error.

Okay. I'm not yet clear on something about it. I guess it's because
I was converting to your syntax from my

Range(Selection, "A1").Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

So I'm sorting based on Column 1. Okay, I'll change it to:

With myRng
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With

Yes, I just tried it, and that works fine. Sorry for being dense!

=====================
I like typing:

with worksheets("sheet999")
set myRng = .range(.cells(1,1),.cells(ilastrow, ilastcol))
end with

But if you like you can qualify each range reference:

set myrng = worksheets("sheet999").range(worksheets("sheet999" ).cells(1,1), _
worksheets("sheet999").cells(ilastrow, ilastcol))

Okay, I see what you like about it. I agree that it's cleaner.

==========
I'm not sure what you're refreshing that causes the warning, but
lots of times, you can suppress warnings by:

application.displayalerts = false
'your code that causes the warning message here
application.displayalerts = true

I'll see how that works. I'm painstakingly building this macro
line-by-line, and I'm not quite there yet. I had a macro earlier
that worked, but I've completely changed my sheet around since
then -- and that macro was not at all optimal, anyway.

Another option would be to remove your subtotals, add your rows,
and reinsert the subtotal rows.

Well, it's formatted a certain way and has some complex calculated
fields, not just totals, so I'd rather not use that approach. I
also could temporarily move it to Row 1, then move it back. If I
do, I think I'll want to turn off the display refresh during that
part of the macro, because that will grate on my sensibilities for
some reason I can't quite put into words. I suppose the reason
is that I'm a purist at heart, but I know too little about what
I'm doing with VBA to be able to live up to my high demands
of myself for creating robust and reusable code.

Or insert your new data above the subtotal rows.

The problem is, I don't know how long the new data is until I refresh
that sheet's data source. The source is a CSV file, so I can't
use fancy VBA code to inspect it first, either.

David McRitchie has some notes about not having to adjust
formulas when additional rows are inserted:

http://www.mvps.org/dmcritchie/excel...row.htm#offset

Bookmarked! Thanks again.

Dallman Ross

[Prior stuff deleted]

--

Dave Peterson

In , Dave Peterson
spake thusly:

I've used this technique when I import files.

I get the data and put it in row 3. I add headers to row 2.
I add autofilters to that range (A2:X9999). But I put all my
subtotals in row 1. (and set rows to repeat at top to 1:2 and
freeze panes so that rows 1 and 2 are always visible).

I've thought about that, based on something someone else said last
week, but I haven't yet succeeded in weaning myself from where they
are now. I like them there! Oh, well.

As for pivot tables, I haven't learned enough about them to get
that to work for my needs. It's something I want to get to. But
meanwhile, I have what I want; I just want to automate the
refresh.

Speaking of auto-filters, my plan with my macro is to turn them off --
I listed the code where I did that -- then do my various refreshes,
etc., then turn them on. But maybe I should just leave them on but
disable them. Not sure how to do that or if it makes much difference.

My latest question is, how do I do a loop with two sheetnames.
The two a

myCSV = "2006 Realized - CSV Data"
and
myCSV = "Current - CSV Data"


And what I'll do twice is:

With Worksheets(myCSV)
'ActiveSheet.Visible = True
ActiveSheet.Unprotect

Selection.QueryTable.Refresh BackgroundQuery:=False

ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'ActiveSheet.Visible = False
End With



Also, when I run that code above, the macro stops and waits
me to hit the Enter key to accept the suggested name for the
query refresh. I don't know why. (And I just got the
circular-reference warning trying my half-complete macro
up to this point. I'll have to try that warning-shut-off
syntax you suggested might help.)

Thanks for letting me pick your brain, Dave!

Dallman Ross

Maybe just doing the equivalent of Data|ShowAll would be sufficient.

And you could use:

dim iCtr as long
dim wksNames as variant
wksnames = array(""2006 Realized - CSV Data", "Current - CSV Data")

for ictr = lbound(wksnames) to ubound(wksnames)
with worksheets(wksnames(ictr))
'do a bunch of stuff
end with
next ictr

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

I've used this technique when I import files.

I get the data and put it in row 3. I add headers to row 2.
I add autofilters to that range (A2:X9999). But I put all my
subtotals in row 1. (and set rows to repeat at top to 1:2 and
freeze panes so that rows 1 and 2 are always visible).

I've thought about that, based on something someone else said last
week, but I haven't yet succeeded in weaning myself from where they
are now. I like them there! Oh, well.

As for pivot tables, I haven't learned enough about them to get
that to work for my needs. It's something I want to get to. But
meanwhile, I have what I want; I just want to automate the
refresh.

Speaking of auto-filters, my plan with my macro is to turn them off --
I listed the code where I did that -- then do my various refreshes,
etc., then turn them on. But maybe I should just leave them on but
disable them. Not sure how to do that or if it makes much difference.

My latest question is, how do I do a loop with two sheetnames.
The two a

myCSV = "2006 Realized - CSV Data"
and
myCSV = "Current - CSV Data"

And what I'll do twice is:

With Worksheets(myCSV)
'ActiveSheet.Visible = True
ActiveSheet.Unprotect

Selection.QueryTable.Refresh BackgroundQuery:=False

ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'ActiveSheet.Visible = False
End With

Also, when I run that code above, the macro stops and waits
me to hit the Enter key to accept the suggested name for the
query refresh. I don't know why. (And I just got the
circular-reference warning trying my half-complete macro
up to this point. I'll have to try that warning-shut-off
syntax you suggested might help.)

Thanks for letting me pick your brain, Dave!

Dallman Ross

--

Dave Peterson

In , Dave Peterson
spake thusly:

Maybe just doing the equivalent of Data|ShowAll would be sufficient.

Okay, that's promising, but if all is already displayed, I get
an error. So I need an if-statement. Don't know how to formulate
it. Hoping you'll show me.

And you could use:

dim iCtr as long
dim wksNames as variant
wksnames = array(""2006 Realized - CSV Data", "Current - CSV Data")

for ictr = lbound(wksnames) to ubound(wksnames)
with worksheets(wksnames(ictr))
'do a bunch of stuff
end with
next ictr

Yes, that's excellent. It works well, once I figured out a
couple of critical things. First, I didn't know about

.Activate

but lucked out in finding it in the VBA Help pages when I
couldn't figure out why my directive to unprotect the sheet
wasn't working. Second, my query refresh wouldn't work and
caused a debug error once I inserted it into this "do stuff"
part of the loop. I flailed around for a while with Google
and help pages to no avail. When I was about to give up,
I finally tried this more or less by accident from the help
pages:

.QueryTables(1).Refresh BackgroundQuery:=False

and, lo! it worked. (What I'd ever want instead of (1),
I couldn't tell you.) :-)

N.B.: That was instead of this, which was suddenly barfing:
Selection.QueryTable.Refresh BackgroundQuery:=False

The macro still stops at the refresh and waits for me to
hit the Enter key to accept the name of the data source
that's correctly displayed in the pop-up window.
I would like it if it didn't wait for me to pound on the
Enter key. But that is not critical.

I suppose I also wouldn't mind a "do while" thing with the
variant instead of setting a counter, just as a matter
of style and to build my burgeoning macro know-how.

Okay, this thing is really starting to get somewhere!
Thanks, Dave.

Dallman Ross

In , Dallman Ross <dman@localhost.
spake thusly:

In , Dave Peterson
spake thusly:

Maybe just doing the equivalent of Data|ShowAll would be
sufficient.

Okay, that's promising, but if all is already displayed, I get an
error. So I need an if-statement. Don't know how to formulate
it. Hoping you'll show me.

I found this!

http://www.contextures.com/xlautofilter03.html

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

I actually had tried something very similar, but it hand't worked.
I had left off "ActiveSheet" and started with the dots.

-dman-

It sounds like you're in business.

Good luck with the tweaks.

Dallman Ross wrote:

In , Dallman Ross <dman@localhost.
spake thusly:

In , Dave Peterson
spake thusly:

Maybe just doing the equivalent of Data|ShowAll would be
sufficient.

Okay, that's promising, but if all is already displayed, I get an
error. So I need an if-statement. Don't know how to formulate
it. Hoping you'll show me.

I found this!

http://www.contextures.com/xlautofilter03.html

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

I actually had tried something very similar, but it hand't worked.
I had left off "ActiveSheet" and started with the dots.

-dman-

--

Dave Peterson

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub
-----------------

Okay, is anything like that possible?

Thanks,
-dman-

Nope.

But you could define another variable.

Option Explicit
Sub RGUpdate()

Dim LastRows As Variant
Dim iCtr As Long
Dim wsNames As Variant
Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

wsNames = Array(csvRG, csvUG)
ReDim LastRows(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
'.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:

LastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub
-----------------

Okay, is anything like that possible?

Thanks,
-dman-

--

Dave Peterson

Hi Dave, jsd219 here. i just saw that posted to here minutes ago so i
thought i would check inwith you. i am struggling with w script i am
trying to write and you wrote me something kinda similiar so i was
hoping you could help figure this out. it involves checking multiple
columns.

God bless
jsd219


Dave Peterson wrote:
Nope.

But you could define another variable.

Option Explicit
Sub RGUpdate()

Dim LastRows As Variant
Dim iCtr As Long
Dim wsNames As Variant
Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

wsNames = Array(csvRG, csvUG)
ReDim LastRows(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
'.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:

LastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub
-----------------

Okay, is anything like that possible?

Thanks,
-dman-

--

Dave Peterson

Good stuff, Dave. That works out. (Had to figure out that the
values for iCtr in LastRows(iCtr) would be, in the case of my
meager two loops, 0 and 1, but that wasn't that hard to suss out.)

Speaking of figuring out, how to I tell VBA to print the
result to the screen so I can test things?

Also, what is the VBA equivalent for statements such
as "continue" or "break" in some other languages, e.g.,
for working with loops?

Dallman

========================
In , Dave Peterson
spake thusly:

Nope.

But you could define another variable.

Option Explicit
Sub RGUpdate()

Dim LastRows As Variant
Dim iCtr As Long
Dim wsNames As Variant
Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

wsNames = Array(csvRG, csvUG)
ReDim LastRows(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
'.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:

LastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub
-----------------

Okay, is anything like that possible?

Thanks,
-dman-

You can use
msgbox sometextvariablehere
or
debug.print sometextvariablehere

And to exit a loop, you can use "exit for" or "exit do".

I try not to use Continue (like in Fortran????). I just include the equivalent
of an if statement within the loop:

do
if something = false then
'do nothing
else
'do something
end if
if somethingelse = true then
exit do
end if
loop


Dallman Ross wrote:

Good stuff, Dave. That works out. (Had to figure out that the
values for iCtr in LastRows(iCtr) would be, in the case of my
meager two loops, 0 and 1, but that wasn't that hard to suss out.)

Speaking of figuring out, how to I tell VBA to print the
result to the screen so I can test things?

Also, what is the VBA equivalent for statements such
as "continue" or "break" in some other languages, e.g.,
for working with loops?

Dallman

========================
In , Dave Peterson
spake thusly:

Nope.

But you could define another variable.

Option Explicit
Sub RGUpdate()

Dim LastRows As Variant
Dim iCtr As Long
Dim wsNames As Variant
Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

wsNames = Array(csvRG, csvUG)
ReDim LastRows(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
'.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:

LastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub
-----------------

Okay, is anything like that possible?

Thanks,
-dman-


--

Dave Peterson

The msgbox will popup a message that you'll have to dismiss.

The debug.print will appear in the immediate window of the VBE.

Dave Peterson wrote:

You can use
msgbox sometextvariablehere
or
debug.print sometextvariablehere

And to exit a loop, you can use "exit for" or "exit do".

I try not to use Continue (like in Fortran????). I just include the equivalent
of an if statement within the loop:

do
if something = false then
'do nothing
else
'do something
end if
if somethingelse = true then
exit do
end if
loop

Dallman Ross wrote:

Good stuff, Dave. That works out. (Had to figure out that the
values for iCtr in LastRows(iCtr) would be, in the case of my
meager two loops, 0 and 1, but that wasn't that hard to suss out.)

Speaking of figuring out, how to I tell VBA to print the
result to the screen so I can test things?

Also, what is the VBA equivalent for statements such
as "continue" or "break" in some other languages, e.g.,
for working with loops?

Dallman

========================
In , Dave Peterson
spake thusly:

Nope.

But you could define another variable.

Option Explicit
Sub RGUpdate()

Dim LastRows As Variant
Dim iCtr As Long
Dim wsNames As Variant
Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

wsNames = Array(csvRG, csvUG)
ReDim LastRows(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
'.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:

LastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub
-----------------

Okay, is anything like that possible?

Thanks,
-dman-


--

Dave Peterson

--

Dave Peterson

In , Dave Peterson
spake thusly:

Dallman Ross wrote:

[H]ow to I tell VBA to print the
result to the screen so I can test things?

You can use
msgbox sometextvariablehere
or
debug.print sometextvariablehere

Okay, I'll try these. I saw your further clarification as well.
Thanks much.

My question about break/continue, etc., isn't from the FORTRAN
world, though I did take a FORTRAN class in college in 1980.
(I can't remember a thing about it.) But I am a Unix scripter.
So I have quite a fair bit of experience with syntax from,
e.g., the Bourne shell under Unix.


By the way, in you sample code snippet you put in declarations:


Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

I have a question about it. I actually had that originally,
then took out the Dim statements because in testing I found it
worked fine without them and with just the Sets. You put
them back. So I, also, put them back. But how come it
works without them?


Now I've started to make another loop higher up in the macro.
I've run into trouble and need help to get it working.

Previously, we had "Dim whatever As Range"; but now I want
that to be in a loop as well. So I tried:

'snip stuff

Dim wsRG As Worksheet
Dim wsUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

' Loop through regular worksheets
Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant

wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

' next line barfs <======================
wsRng(iCtr) = .Range("A1", .Cells(wsLastRows, wsLastCols))

'snip rest


Okay, so how do I fix that type mismatch?

Dallman Ross

I have "Option Explicit" at the top of each of my modules.

Inside the VBE, tools|Options|Editor Tab|Check "require variable declaration"

Without that "option explicit" at the top of the module, you don't have to
declare any variable. But that means that you can spend hours trying to find
why this doesn't work the way you want:

ctrl = ctr1+1
(ctr-ell vs. ctrl-one).

There are other benefits to declaring your variables, too.

===========

First, wslastrows is an array, same with wslastcols. You only want to use the
lastrow of that worksheet you're processing.

So you'd want something like:
wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

But I'm betting that you want wsRng to be an array of ranges. If that's the
case, then:

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

Without the Set in that statement, wsRng would be an array of arrays of values.

dim temp as variant
temp = range("a1:c9").value
would produce a 9 column by 3 row array of the values in that range

set Temp = range("a1:c9")
would produce a range variable (with all its properties).


Dallman Ross wrote:

In , Dave Peterson
spake thusly:

Dallman Ross wrote:

[H]ow to I tell VBA to print the
result to the screen so I can test things?

You can use
msgbox sometextvariablehere
or
debug.print sometextvariablehere

Okay, I'll try these. I saw your further clarification as well.
Thanks much.

My question about break/continue, etc., isn't from the FORTRAN
world, though I did take a FORTRAN class in college in 1980.
(I can't remember a thing about it.) But I am a Unix scripter.
So I have quite a fair bit of experience with syntax from,
e.g., the Bourne shell under Unix.

By the way, in you sample code snippet you put in declarations:

Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

I have a question about it. I actually had that originally,
then took out the Dim statements because in testing I found it
worked fine without them and with just the Sets. You put
them back. So I, also, put them back. But how come it
works without them?

Now I've started to make another loop higher up in the macro.
I've run into trouble and need help to get it working.

Previously, we had "Dim whatever As Range"; but now I want
that to be in a loop as well. So I tried:

'snip stuff

Dim wsRG As Worksheet
Dim wsUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

' Loop through regular worksheets
Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant

wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

' next line barfs <======================
wsRng(iCtr) = .Range("A1", .Cells(wsLastRows, wsLastCols))

'snip rest


Okay, so how do I fix that type mismatch?

Dallman Ross

--

Dave Peterson

ctrl = ctr1+1
(ctr-ell vs. ctr-one plus one).

Dave Peterson wrote:

I have "Option Explicit" at the top of each of my modules.

Inside the VBE, tools|Options|Editor Tab|Check "require variable declaration"

Without that "option explicit" at the top of the module, you don't have to
declare any variable. But that means that you can spend hours trying to find
why this doesn't work the way you want:

ctrl = ctr1+1
(ctr-ell vs. ctrl-one).

There are other benefits to declaring your variables, too.

===========

First, wslastrows is an array, same with wslastcols. You only want to use the
lastrow of that worksheet you're processing.

So you'd want something like:
wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

But I'm betting that you want wsRng to be an array of ranges. If that's the
case, then:

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

Without the Set in that statement, wsRng would be an array of arrays of values.

dim temp as variant
temp = range("a1:c9").value
would produce a 9 column by 3 row array of the values in that range

set Temp = range("a1:c9")
would produce a range variable (with all its properties).

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

Dallman Ross wrote:

[H]ow to I tell VBA to print the
result to the screen so I can test things?

You can use
msgbox sometextvariablehere
or
debug.print sometextvariablehere

Okay, I'll try these. I saw your further clarification as well.
Thanks much.

My question about break/continue, etc., isn't from the FORTRAN
world, though I did take a FORTRAN class in college in 1980.
(I can't remember a thing about it.) But I am a Unix scripter.
So I have quite a fair bit of experience with syntax from,
e.g., the Bourne shell under Unix.

By the way, in you sample code snippet you put in declarations:

Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

I have a question about it. I actually had that originally,
then took out the Dim statements because in testing I found it
worked fine without them and with just the Sets. You put
them back. So I, also, put them back. But how come it
works without them?

Now I've started to make another loop higher up in the macro.
I've run into trouble and need help to get it working.

Previously, we had "Dim whatever As Range"; but now I want
that to be in a loop as well. So I tried:

'snip stuff

Dim wsRG As Worksheet
Dim wsUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

' Loop through regular worksheets
Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant

wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

' next line barfs <======================
wsRng(iCtr) = .Range("A1", .Cells(wsLastRows, wsLastCols))

'snip rest


Okay, so how do I fix that type mismatch?

Dallman Ross

--

Dave Peterson

--

Dave Peterson

In , Dave Peterson
spake thusly:

I have "Option Explicit" at the top of each of my modules.
Inside the VBE, tools|Options|Editor Tab|Check "require variable
declaration"

Okay, great explanation, Dave. I've put that in now too. I'm
beginning to see the light! :-)


But I'm betting that you want wsRng to be an array of ranges. If
that's the case, then:

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

Yes, certainly correct on your bet. And, again, good explanation!
[Snipped here; cavilers and pundits can look down below for the
full article again. -dr]


I've polished it up again. The bad news is, that line still
doesn't work. :-( Only now it gives a different sort of error:
Application-defined or object-defined error ("1004"). I'll
state for the record that this is Excel 2002 under XP Pro.

Here's that section now:
---------------------------------------------------
Option Explicit
Sub RGUpdate()
'
' Update Realized Gains and Restate Current Positions
' Macro by Dallman Ross w/ massive helpful kibitzing from Dave Peterson
' Last edited 30-Oct-2006

Dim wsRG As Worksheet
Dim wsUG As Worksheet
Dim csvRG As Worksheet
Dim csvUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")
Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

' Loop through regular worksheets
Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant

wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

'Debug.Print iCtr, wsLastRows(iCtr), wsLastCols(iCtr)

'Below line still barfs. :-(
Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

.Cells.EntireColumn.Hidden = False

If .FilterMode Then
.ShowAllData
End If
End With

With wsRng(iCtr)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With
Next iCtr

' bottom half of macro snipped

End Sub
---------------------------------------------------

Muchas gracias,
Dallman


================================================== ======
In , Dave Peterson
spake thusly:

I have "Option Explicit" at the top of each of my modules.

Inside the VBE, tools|Options|Editor Tab|Check "require variable
declaration"

Without that "option explicit" at the top of the module, you
don't have to declare any variable. But that means that you can
spend hours trying to find why this doesn't work the way you
want:

ctrl = ctr1+1
(ctr-ell vs. ctrl-one).

There are other benefits to declaring your variables, too.

===========

First, wslastrows is an array, same with wslastcols. You only
want to use the lastrow of that worksheet you're processing.

So you'd want something like:
wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

But I'm betting that you want wsRng to be an array of ranges. If
that's the case, then:

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

Without the Set in that statement, wsRng would be an array of
arrays of values.

dim temp as variant
temp = range("a1:c9").value
would produce a 9 column by 3 row array of the values in that range

set Temp = range("a1:c9")
would produce a range variable (with all its properties).


Dallman Ross wrote:

In , Dave Peterson
spake thusly:

Dallman Ross wrote:

[H]ow to I tell VBA to print the
result to the screen so I can test things?

You can use
msgbox sometextvariablehere
or
debug.print sometextvariablehere

Okay, I'll try these. I saw your further clarification as
well. Thanks much.

My question about break/continue, etc., isn't from the FORTRAN
world, though I did take a FORTRAN class in college in 1980.
(I can't remember a thing about it.) But I am a Unix scripter.
So I have quite a fair bit of experience with syntax from,
e.g., the Bourne shell under Unix.

By the way, in you sample code snippet you put in declarations:

Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

I have a question about it. I actually had that originally,
then took out the Dim statements because in testing I found it
worked fine without them and with just the Sets. You put
them back. So I, also, put them back. But how come it
works without them?

Now I've started to make another loop higher up in the macro.
I've run into trouble and need help to get it working.

Previously, we had "Dim whatever As Range"; but now I want
that to be in a loop as well. So I tried:

'snip stuff

Dim wsRG As Worksheet
Dim wsUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

' Loop through regular worksheets
Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant

wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

' next line barfs <======================
wsRng(iCtr) = .Range("A1", .Cells(wsLastRows, wsLastCols))

'snip rest


Okay, so how do I fix that type mismatch?

Dallman Ross

I didn't notice this before, but take a look at these two lines:

wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

Do you see anything wrong?

Scroll down for an answer
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

You're putting a column number in a "row" variable and a row number in a "col"
variable. If the lastrow is greater than 256, then when you refer to that as a
column, you'll get an error.

wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

<<snipped

In , Dave Peterson
spake thusly:

I didn't notice this before, but take a look at these two lines:

wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

Do you see anything wrong?

Man! I looked and I looked, and I didn't see it.

Scroll down for an answer

:-)

You're putting a column number in a "row" variable and a row
number in a "col" variable. If the lastrow is greater than 256,
then when you refer to that as a column, you'll get an error.

Holy moly! That rocks, that you found that one. This thing
is starting to be really cool for me. Thanks ever again, Dave!

Dallman

About three weeks ago In , Dave
Peterson spake thusly:

[snip to quote of some lines from a macro of mine]
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

You're putting a column number in a "row" variable and a row
number in a "col" variable. If the lastrow is greater than 256,
then when you refer to that as a column, you'll get an error.

wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

<<snipped

The above was part of Dave's help offered over a couple of weeks
earlier this month and last month getting me up-to-speed on a
somewhat long macro. It now works, more or less doing what I
want. I'm grateful. But I'm still fuzzy on enough parts of it to
want to post the whole thing here as it now stands. My hope is,
Dave or someone else will give me further crib notes on what parts
of it might be improved, code-wise, or might be outright illogical
or wrong. Suggestions gladly entertained!

One thing I can say is, part of it near the end is supposed to
place me in the bottom data-row, but doesn't seem to work. That's
just for looks when the macro is ending, so I don't have to scroll
down by hand in my sheet. But I can't figure out why that part
isn't working right.

The theory behind this macro is: unfilter two main sheets in
my workbook and unhide their hidden cols; sort to set a keyed
reference column to its proper order; go to the two normally hidden
sheets referenced from the two main sheets; unhide/unprotect them
and refresh their data queries; update ranges; rehide/re-protect;
go back to the two main sheets and update the row count to fit
the newly updated referenced data sheets; pull down formulas and
references; convert formulas to values for speed, in all but the
first row; re-sort to desired nominal view; re-auto-filter; set the
active cells to the bottom data rows (doesn't work right).

-dman-

------------------------ start of macro -------------------------

Option Explicit
Sub RGUpdate()
'
' Update Realized Gains and Restate Current Positions
' Macro by Dallman Ross w/ massive kibitzing by Dave Peterson
' Last edited 18-Nov-2006

'************************************************* ********
Dim wsRG As Worksheet
Dim wsUG As Worksheet
Dim csvRG As Worksheet
Dim csvUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant
'************************************************* ********


' Loop through regular worksheets

'************************************************* ********
wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
.Cells.EntireColumn.Hidden = False

If .FilterMode Then
.ShowAllData
End If

wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row
Debug.Print iCtr, wsLastRows(iCtr), wsLastCols(iCtr)

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))
End With

With wsRng(iCtr)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the first loop."



'************************************************* ********
Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")
Dim csvLastRows As Variant
'************************************************* ********



' Loop through csv source worksheets

'************************************************* ********
wsNames = Array(csvRG, csvUG)
ReDim csvLastRows(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

Application.DisplayAlerts = False
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Visible = True
.Activate
.Range("A1").Select 'set focus
.Unprotect

.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

csvLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
.Visible = False
End With
Next iCtr

Debug.Print "We're done with the second loop."



' Loop again through regular worksheets _
adding or deleting rows, as needed

'************************************************* ********
Dim AdjustRows As Long
wsNames = Array(wsRG, wsUG)
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
AdjustRows = csvLastRows(iCtr) - wsLastRows(iCtr)
Debug.Print AdjustRows

If AdjustRows Then

' Add or delete rows
If AdjustRows 0 Then
.Range(Rows(wsLastRows(iCtr) + 1), _
Rows(wsLastRows(iCtr)). _
Offset(AdjustRows, 0)).EntireRow.Insert
Else
.Range(Rows(wsLastRows(iCtr) + AdjustRows + 1), _
Rows(wsLastRows(iCtr)). _
Offset(0, 0)).EntireRow.Delete

End If

' Copy first data row
.Range("A2", .Cells(2, wsLastCols(iCtr))).Copy

' Paste to fill out sheet range; _
convert to values as of Row 3

.Range("A3", .Cells(csvLastRows(iCtr), _
wsLastCols(iCtr))).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If

.Range("A1", .Cells(wsLastRows(iCtr) + AdjustRows, _
wsLastCols(iCtr))).Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("J2"), Order2:=xlAscending, _
Key3:=Range("M2"), Order3:=xlDescending, _
header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the range-adjustment loop."


' Sort and pre-filter main Realized Gains sheet

With wsRG
.Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("P2"), Order2:=xlAscending, _
Key3:=Range("I2"), Order3:=xlAscending, _
header:=xlYes
Selection.AutoFilter Field:=6, Criteria1:="<-"
Selection.AutoFilter Field:=23, Criteria1:="<1000"
End With


'************************************************* ********
wsNames = Array(wsRG, wsUG)
'************************************************* ********

' Final loop through regular worksheets _
hiding rows, positioning cursor

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
Application.Run "HideCols"

'Go to bottom -- doesn't work right for some reason
.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select
End With
Next iCtr

Debug.Print "We're done with HideCols loop."

Application.DisplayAlerts = True
End Sub

Without reading all the code (too much for me!), this line looks funny:

..Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select

I would think that you'd want some columns in there, too.

Dallman Ross wrote:

About three weeks ago In , Dave
Peterson spake thusly:

[snip to quote of some lines from a macro of mine]
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

You're putting a column number in a "row" variable and a row
number in a "col" variable. If the lastrow is greater than 256,
then when you refer to that as a column, you'll get an error.

wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

<<snipped

The above was part of Dave's help offered over a couple of weeks
earlier this month and last month getting me up-to-speed on a
somewhat long macro. It now works, more or less doing what I
want. I'm grateful. But I'm still fuzzy on enough parts of it to
want to post the whole thing here as it now stands. My hope is,
Dave or someone else will give me further crib notes on what parts
of it might be improved, code-wise, or might be outright illogical
or wrong. Suggestions gladly entertained!

One thing I can say is, part of it near the end is supposed to
place me in the bottom data-row, but doesn't seem to work. That's
just for looks when the macro is ending, so I don't have to scroll
down by hand in my sheet. But I can't figure out why that part
isn't working right.

The theory behind this macro is: unfilter two main sheets in
my workbook and unhide their hidden cols; sort to set a keyed
reference column to its proper order; go to the two normally hidden
sheets referenced from the two main sheets; unhide/unprotect them
and refresh their data queries; update ranges; rehide/re-protect;
go back to the two main sheets and update the row count to fit
the newly updated referenced data sheets; pull down formulas and
references; convert formulas to values for speed, in all but the
first row; re-sort to desired nominal view; re-auto-filter; set the
active cells to the bottom data rows (doesn't work right).

-dman-

------------------------ start of macro -------------------------

Option Explicit
Sub RGUpdate()
'
' Update Realized Gains and Restate Current Positions
' Macro by Dallman Ross w/ massive kibitzing by Dave Peterson
' Last edited 18-Nov-2006

'************************************************* ********
Dim wsRG As Worksheet
Dim wsUG As Worksheet
Dim csvRG As Worksheet
Dim csvUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant
'************************************************* ********

' Loop through regular worksheets

'************************************************* ********
wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
.Cells.EntireColumn.Hidden = False

If .FilterMode Then
.ShowAllData
End If

wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row
Debug.Print iCtr, wsLastRows(iCtr), wsLastCols(iCtr)

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))
End With

With wsRng(iCtr)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the first loop."

'************************************************* ********
Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")
Dim csvLastRows As Variant
'************************************************* ********

' Loop through csv source worksheets

'************************************************* ********
wsNames = Array(csvRG, csvUG)
ReDim csvLastRows(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

Application.DisplayAlerts = False
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Visible = True
.Activate
.Range("A1").Select 'set focus
.Unprotect

.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

csvLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
.Visible = False
End With
Next iCtr

Debug.Print "We're done with the second loop."

' Loop again through regular worksheets _
adding or deleting rows, as needed

'************************************************* ********
Dim AdjustRows As Long
wsNames = Array(wsRG, wsUG)
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
AdjustRows = csvLastRows(iCtr) - wsLastRows(iCtr)
Debug.Print AdjustRows

If AdjustRows Then

' Add or delete rows
If AdjustRows 0 Then
.Range(Rows(wsLastRows(iCtr) + 1), _
Rows(wsLastRows(iCtr)). _
Offset(AdjustRows, 0)).EntireRow.Insert
Else
.Range(Rows(wsLastRows(iCtr) + AdjustRows + 1), _
Rows(wsLastRows(iCtr)). _
Offset(0, 0)).EntireRow.Delete

End If

' Copy first data row
.Range("A2", .Cells(2, wsLastCols(iCtr))).Copy

' Paste to fill out sheet range; _
convert to values as of Row 3

.Range("A3", .Cells(csvLastRows(iCtr), _
wsLastCols(iCtr))).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If

.Range("A1", .Cells(wsLastRows(iCtr) + AdjustRows, _
wsLastCols(iCtr))).Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("J2"), Order2:=xlAscending, _
Key3:=Range("M2"), Order3:=xlDescending, _
header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the range-adjustment loop."

' Sort and pre-filter main Realized Gains sheet

With wsRG
.Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("P2"), Order2:=xlAscending, _
Key3:=Range("I2"), Order3:=xlAscending, _
header:=xlYes
Selection.AutoFilter Field:=6, Criteria1:="<-"
Selection.AutoFilter Field:=23, Criteria1:="<1000"
End With

'************************************************* ********
wsNames = Array(wsRG, wsUG)
'************************************************* ********

' Final loop through regular worksheets _
hiding rows, positioning cursor

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
Application.Run "HideCols"

'Go to bottom -- doesn't work right for some reason
.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select
End With
Next iCtr

Debug.Print "We're done with HideCols loop."

Application.DisplayAlerts = True
End Sub

--

Dave Peterson

In , Dave Peterson
spake thusly:

Without reading all the code (too much for me!), this line looks
funny:

Okay, I can't look a gift horse in the mouth; but if you did
find the time, the last half is the scaggiest for me. :-)
The third loop (of four) on. Maybe others here care to
comment on the style or any obvious weaknesses in the
algorithm I chose.


.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select

I would think that you'd want some columns in there, too.

Hmm. I thought the last expression was for columns. I
think I don't quite know how to read the syntax. I did
it all mostly by trial-and-error until it worked (more or
less).

Thanks for any clues.

-dman-

================================================== ================
Dallman Ross wrote:
I'm grateful [about lots of help received so far, mostly from
Dave Peterson, with a macro]. But I'm still fuzzy on enough
parts of it to want to post the whole thing here as it now
stands. My hope is, Dave or someone else will give me further
crib notes on what parts of it might be improved, code-wise,
or might be outright illogical or wrong. Suggestions gladly
entertained!


One thing I can say is, part of it near the end is supposed to
place me in the bottom data-row, but doesn't seem to work. That's
just for looks when the macro is ending, so I don't have to scroll
down by hand in my sheet. But I can't figure out why that part
isn't working right.

The theory behind this macro is: unfilter two main sheets in
my workbook and unhide their hidden cols; sort to set a keyed
reference column to its proper order; go to the two normally hidden
sheets referenced from the two main sheets; unhide/unprotect them
and refresh their data queries; update ranges; rehide/re-protect;
go back to the two main sheets and update the row count to fit
the newly updated referenced data sheets; pull down formulas and
references; convert formulas to values for speed, in all but the
first row; re-sort to desired nominal view; re-auto-filter; set the
active cells to the bottom data rows (doesn't work right).

-dman-

------------------------ start of macro -------------------------

Option Explicit
Sub RGUpdate()
'
' Update Realized Gains and Restate Current Positions
' Macro by Dallman Ross w/ massive kibitzing by Dave Peterson
' Last edited 18-Nov-2006

'************************************************* ********
Dim wsRG As Worksheet
Dim wsUG As Worksheet
Dim csvRG As Worksheet
Dim csvUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant
'************************************************* ********

' Loop through regular worksheets

'************************************************* ********
wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
.Cells.EntireColumn.Hidden = False

If .FilterMode Then
.ShowAllData
End If

wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row
Debug.Print iCtr, wsLastRows(iCtr), wsLastCols(iCtr)

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))
End With

With wsRng(iCtr)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the first loop."

'************************************************* ********
Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")
Dim csvLastRows As Variant
'************************************************* ********

' Loop through csv source worksheets

'************************************************* ********
wsNames = Array(csvRG, csvUG)
ReDim csvLastRows(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

Application.DisplayAlerts = False
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Visible = True
.Activate
.Range("A1").Select 'set focus
.Unprotect

.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

csvLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
.Visible = False
End With
Next iCtr

Debug.Print "We're done with the second loop."

' Loop again through regular worksheets _
adding or deleting rows, as needed

'************************************************* ********
Dim AdjustRows As Long
wsNames = Array(wsRG, wsUG)
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
AdjustRows = csvLastRows(iCtr) - wsLastRows(iCtr)
Debug.Print AdjustRows

If AdjustRows Then

' Add or delete rows
If AdjustRows 0 Then
.Range(Rows(wsLastRows(iCtr) + 1), _
Rows(wsLastRows(iCtr)). _
Offset(AdjustRows, 0)).EntireRow.Insert
Else
.Range(Rows(wsLastRows(iCtr) + AdjustRows + 1), _
Rows(wsLastRows(iCtr)). _
Offset(0, 0)).EntireRow.Delete

End If

' Copy first data row
.Range("A2", .Cells(2, wsLastCols(iCtr))).Copy

' Paste to fill out sheet range; _
convert to values as of Row 3

.Range("A3", .Cells(csvLastRows(iCtr), _
wsLastCols(iCtr))).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If

.Range("A1", .Cells(wsLastRows(iCtr) + AdjustRows, _
wsLastCols(iCtr))).Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("J2"), Order2:=xlAscending, _
Key3:=Range("M2"), Order3:=xlDescending, _
header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the range-adjustment loop."

' Sort and pre-filter main Realized Gains sheet

With wsRG
.Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("P2"), Order2:=xlAscending, _
Key3:=Range("I2"), Order3:=xlAscending, _
header:=xlYes
Selection.AutoFilter Field:=6, Criteria1:="<-"
Selection.AutoFilter Field:=23, Criteria1:="<1000"
End With

'************************************************* ********
wsNames = Array(wsRG, wsUG)
'************************************************* ********

' Final loop through regular worksheets _
hiding rows, positioning cursor

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
Application.Run "HideCols"

'Go to bottom -- doesn't work right for some reason
.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select
End With
Next iCtr

Debug.Print "We're done with HideCols loop."

Application.DisplayAlerts = True
End Sub

I put this in the immediate window:

Range(Cells(15), Cells(2)).Select

and there were no columns selected.

If you break down your code into smaller pieces and ask about those smaller
pieces, I think a lot of people will jump in.

But to set up test data (with or without problem data) and to try to generally
debug the procedure is a task many won't undertake.

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

Without reading all the code (too much for me!), this line looks
funny:

Okay, I can't look a gift horse in the mouth; but if you did
find the time, the last half is the scaggiest for me. :-)
The third loop (of four) on. Maybe others here care to
comment on the style or any obvious weaknesses in the
algorithm I chose.

.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select

I would think that you'd want some columns in there, too.

Hmm. I thought the last expression was for columns. I
think I don't quite know how to read the syntax. I did
it all mostly by trial-and-error until it worked (more or
less).

Thanks for any clues.

-dman-

================================================== ================
Dallman Ross wrote:
I'm grateful [about lots of help received so far, mostly from
Dave Peterson, with a macro]. But I'm still fuzzy on enough
parts of it to want to post the whole thing here as it now
stands. My hope is, Dave or someone else will give me further
crib notes on what parts of it might be improved, code-wise,
or might be outright illogical or wrong. Suggestions gladly
entertained!


One thing I can say is, part of it near the end is supposed to
place me in the bottom data-row, but doesn't seem to work. That's
just for looks when the macro is ending, so I don't have to scroll
down by hand in my sheet. But I can't figure out why that part
isn't working right.

The theory behind this macro is: unfilter two main sheets in
my workbook and unhide their hidden cols; sort to set a keyed
reference column to its proper order; go to the two normally hidden
sheets referenced from the two main sheets; unhide/unprotect them
and refresh their data queries; update ranges; rehide/re-protect;
go back to the two main sheets and update the row count to fit
the newly updated referenced data sheets; pull down formulas and
references; convert formulas to values for speed, in all but the
first row; re-sort to desired nominal view; re-auto-filter; set the
active cells to the bottom data rows (doesn't work right).

-dman-

------------------------ start of macro -------------------------

Option Explicit
Sub RGUpdate()
'
' Update Realized Gains and Restate Current Positions
' Macro by Dallman Ross w/ massive kibitzing by Dave Peterson
' Last edited 18-Nov-2006

'************************************************* ********
Dim wsRG As Worksheet
Dim wsUG As Worksheet
Dim csvRG As Worksheet
Dim csvUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant
'************************************************* ********

' Loop through regular worksheets

'************************************************* ********
wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
.Cells.EntireColumn.Hidden = False

If .FilterMode Then
.ShowAllData
End If

wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row
Debug.Print iCtr, wsLastRows(iCtr), wsLastCols(iCtr)

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))
End With

With wsRng(iCtr)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the first loop."

'************************************************* ********
Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")
Dim csvLastRows As Variant
'************************************************* ********

' Loop through csv source worksheets

'************************************************* ********
wsNames = Array(csvRG, csvUG)
ReDim csvLastRows(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

Application.DisplayAlerts = False
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Visible = True
.Activate
.Range("A1").Select 'set focus
.Unprotect

.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

csvLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
.Visible = False
End With
Next iCtr

Debug.Print "We're done with the second loop."

' Loop again through regular worksheets _
adding or deleting rows, as needed

'************************************************* ********
Dim AdjustRows As Long
wsNames = Array(wsRG, wsUG)
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
AdjustRows = csvLastRows(iCtr) - wsLastRows(iCtr)
Debug.Print AdjustRows

If AdjustRows Then

' Add or delete rows
If AdjustRows 0 Then
.Range(Rows(wsLastRows(iCtr) + 1), _
Rows(wsLastRows(iCtr)). _
Offset(AdjustRows, 0)).EntireRow.Insert
Else
.Range(Rows(wsLastRows(iCtr) + AdjustRows + 1), _
Rows(wsLastRows(iCtr)). _
Offset(0, 0)).EntireRow.Delete

End If

' Copy first data row
.Range("A2", .Cells(2, wsLastCols(iCtr))).Copy

' Paste to fill out sheet range; _
convert to values as of Row 3

.Range("A3", .Cells(csvLastRows(iCtr), _
wsLastCols(iCtr))).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If

.Range("A1", .Cells(wsLastRows(iCtr) + AdjustRows, _
wsLastCols(iCtr))).Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("J2"), Order2:=xlAscending, _
Key3:=Range("M2"), Order3:=xlDescending, _
header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the range-adjustment loop."

' Sort and pre-filter main Realized Gains sheet

With wsRG
.Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("P2"), Order2:=xlAscending, _
Key3:=Range("I2"), Order3:=xlAscending, _
header:=xlYes
Selection.AutoFilter Field:=6, Criteria1:="<-"
Selection.AutoFilter Field:=23, Criteria1:="<1000"
End With

'************************************************* ********
wsNames = Array(wsRG, wsUG)
'************************************************* ********

' Final loop through regular worksheets _
hiding rows, positioning cursor

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
Application.Run "HideCols"

'Go to bottom -- doesn't work right for some reason
.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select
End With
Next iCtr

Debug.Print "We're done with HideCols loop."

Application.DisplayAlerts = True
End Sub


--

Dave Peterson

In , Dave Peterson
spake thusly:

I put this in the immediate window:

Range(Cells(15), Cells(2)).Select

and there were no columns selected.

See? Now, I didn't even know that one could type things into
the immediate window to test them. So you taught me something
else useful.

I figured it out: I don't need a range at all. A cells ref will
do for this.

With wsNames(iCtr)
.Activate
Application.Run "HideCols"

'Go to bottom, Column "B"
.Cells(csvLastRows(iCtr) + 1, 2).Select
End With


If you break down your code into smaller pieces and ask about
those smaller pieces, I think a lot of people will jump in.

Okay. I worry about things like how to show all the Dim
statements, etc., when I do that. But I guess I'll deal
with that as I think of specific questions to ask.


But to set up test data (with or without problem data) and to try
to generally debug the procedure is a task many won't undertake.

I wasn't actually expecting people to mock up data to use
for testing the code in this case; rather, just eyeballing
and saying stuff like, "hey, that algorithm doesn't seem
like the best approach there," or "it would be better
to limit what's going on here to the active sheet, which
you could do thus," and so on. But I agree that it's so
long that people aren't likely going to want to jump in.

I'm used to this kind of kibitzing with Unix shell scripts,
which I know a lot more about. That's the kind of thing some of
us do in another group. But I am not wanting to cause someone
who is a good-guy volunteer (such as you!) many long minutes of
head-scratching if more concise questions are better for me to
ask. I'll see over the next days what I can think of to ask more
directly.

I don't really understand all the ReDim, With, and so on,
stuff. I kind of have a vague idea and guess a lot. I
try it, and if it works, I think maybe I'm on the right
track. That's the kind of stuff I was hoping for a big
brother or sister with. :-)

Thanks,
Dallman

==================================================
Dallman Ross wrote:

In , Dave Peterson
spake thusly:

Without reading all the code (too much for me!), this line looks
funny:

Okay, I can't look a gift horse in the mouth; but if you did
find the time, the last half is the scaggiest for me. :-)
The third loop (of four) on. Maybe others here care to
comment on the style or any obvious weaknesses in the
algorithm I chose.

.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select

I would think that you'd want some columns in there, too.

Hmm. I thought the last expression was for columns. I
think I don't quite know how to read the syntax. I did
it all mostly by trial-and-error until it worked (more or
less).

Thanks for any clues.

-dman-

================================================== ================
Dallman Ross wrote:
I'm grateful [about lots of help received so far, mostly from
Dave Peterson, with a macro]. But I'm still fuzzy on enough
parts of it to want to post the whole thing here as it now
stands. My hope is, Dave or someone else will give me further
crib notes on what parts of it might be improved, code-wise,
or might be outright illogical or wrong. Suggestions gladly
entertained!


One thing I can say is, part of it near the end is supposed to
place me in the bottom data-row, but doesn't seem to work. That's
just for looks when the macro is ending, so I don't have to scroll
down by hand in my sheet. But I can't figure out why that part
isn't working right.

The theory behind this macro is: unfilter two main sheets in
my workbook and unhide their hidden cols; sort to set a keyed
reference column to its proper order; go to the two normally hidden
sheets referenced from the two main sheets; unhide/unprotect them
and refresh their data queries; update ranges; rehide/re-protect;
go back to the two main sheets and update the row count to fit
the newly updated referenced data sheets; pull down formulas and
references; convert formulas to values for speed, in all but the
first row; re-sort to desired nominal view; re-auto-filter; set the
active cells to the bottom data rows (doesn't work right).

-dman-

------------------------ start of macro -------------------------

Option Explicit
Sub RGUpdate()
'
' Update Realized Gains and Restate Current Positions
' Macro by Dallman Ross w/ massive kibitzing by Dave Peterson
' Last edited 18-Nov-2006

'************************************************* ********
Dim wsRG As Worksheet
Dim wsUG As Worksheet
Dim csvRG As Worksheet
Dim csvUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant
'************************************************* ********

' Loop through regular worksheets

'************************************************* ********
wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
.Cells.EntireColumn.Hidden = False

If .FilterMode Then
.ShowAllData
End If

wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row
Debug.Print iCtr, wsLastRows(iCtr), wsLastCols(iCtr)

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))
End With

With wsRng(iCtr)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the first loop."

'************************************************* ********
Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")
Dim csvLastRows As Variant
'************************************************* ********

' Loop through csv source worksheets

'************************************************* ********
wsNames = Array(csvRG, csvUG)
ReDim csvLastRows(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

Application.DisplayAlerts = False
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Visible = True
.Activate
.Range("A1").Select 'set focus
.Unprotect

.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

csvLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
.Visible = False
End With
Next iCtr

Debug.Print "We're done with the second loop."

' Loop again through regular worksheets _
adding or deleting rows, as needed

'************************************************* ********
Dim AdjustRows As Long
wsNames = Array(wsRG, wsUG)
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
AdjustRows = csvLastRows(iCtr) - wsLastRows(iCtr)
Debug.Print AdjustRows

If AdjustRows Then

' Add or delete rows
If AdjustRows 0 Then
.Range(Rows(wsLastRows(iCtr) + 1), _
Rows(wsLastRows(iCtr)). _
Offset(AdjustRows, 0)).EntireRow.Insert
Else
.Range(Rows(wsLastRows(iCtr) + AdjustRows + 1), _
Rows(wsLastRows(iCtr)). _
Offset(0, 0)).EntireRow.Delete

End If

' Copy first data row
.Range("A2", .Cells(2, wsLastCols(iCtr))).Copy

' Paste to fill out sheet range; _
convert to values as of Row 3

.Range("A3", .Cells(csvLastRows(iCtr), _
wsLastCols(iCtr))).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If

.Range("A1", .Cells(wsLastRows(iCtr) + AdjustRows, _
wsLastCols(iCtr))).Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("J2"), Order2:=xlAscending, _
Key3:=Range("M2"), Order3:=xlDescending, _
header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the range-adjustment loop."

' Sort and pre-filter main Realized Gains sheet

With wsRG
.Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("P2"), Order2:=xlAscending, _
Key3:=Range("I2"), Order3:=xlAscending, _
header:=xlYes
Selection.AutoFilter Field:=6, Criteria1:="<-"
Selection.AutoFilter Field:=23, Criteria1:="<1000"
End With

'************************************************* ********
wsNames = Array(wsRG, wsUG)
'************************************************* ********

' Final loop through regular worksheets _
hiding rows, positioning cursor

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
Application.Run "HideCols"

'Go to bottom -- doesn't work right for some reason
.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select
End With
Next iCtr

Debug.Print "We're done with HideCols loop."

Application.DisplayAlerts = True
End Sub

The bad news is just eyeballing the code isn't enough--well, for me anyway.
Things can look perfect and there could be something as simple as a typo that
causes trouble.

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

I put this in the immediate window:

Range(Cells(15), Cells(2)).Select

and there were no columns selected.

See? Now, I didn't even know that one could type things into
the immediate window to test them. So you taught me something
else useful.

I figured it out: I don't need a range at all. A cells ref will
do for this.

With wsNames(iCtr)
.Activate
Application.Run "HideCols"

'Go to bottom, Column "B"
.Cells(csvLastRows(iCtr) + 1, 2).Select
End With

If you break down your code into smaller pieces and ask about
those smaller pieces, I think a lot of people will jump in.

Okay. I worry about things like how to show all the Dim
statements, etc., when I do that. But I guess I'll deal
with that as I think of specific questions to ask.

But to set up test data (with or without problem data) and to try
to generally debug the procedure is a task many won't undertake.

I wasn't actually expecting people to mock up data to use
for testing the code in this case; rather, just eyeballing
and saying stuff like, "hey, that algorithm doesn't seem
like the best approach there," or "it would be better
to limit what's going on here to the active sheet, which
you could do thus," and so on. But I agree that it's so
long that people aren't likely going to want to jump in.

I'm used to this kind of kibitzing with Unix shell scripts,
which I know a lot more about. That's the kind of thing some of
us do in another group. But I am not wanting to cause someone
who is a good-guy volunteer (such as you!) many long minutes of
head-scratching if more concise questions are better for me to
ask. I'll see over the next days what I can think of to ask more
directly.

I don't really understand all the ReDim, With, and so on,
stuff. I kind of have a vague idea and guess a lot. I
try it, and if it works, I think maybe I'm on the right
track. That's the kind of stuff I was hoping for a big
brother or sister with. :-)

Thanks,
Dallman

==================================================
Dallman Ross wrote:

In , Dave Peterson
spake thusly:

Without reading all the code (too much for me!), this line looks
funny:

Okay, I can't look a gift horse in the mouth; but if you did
find the time, the last half is the scaggiest for me. :-)
The third loop (of four) on. Maybe others here care to
comment on the style or any obvious weaknesses in the
algorithm I chose.

.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select

I would think that you'd want some columns in there, too.

Hmm. I thought the last expression was for columns. I
think I don't quite know how to read the syntax. I did
it all mostly by trial-and-error until it worked (more or
less).

Thanks for any clues.

-dman-

================================================== ================
Dallman Ross wrote:
I'm grateful [about lots of help received so far, mostly from
Dave Peterson, with a macro]. But I'm still fuzzy on enough
parts of it to want to post the whole thing here as it now
stands. My hope is, Dave or someone else will give me further
crib notes on what parts of it might be improved, code-wise,
or might be outright illogical or wrong. Suggestions gladly
entertained!


One thing I can say is, part of it near the end is supposed to
place me in the bottom data-row, but doesn't seem to work. That's
just for looks when the macro is ending, so I don't have to scroll
down by hand in my sheet. But I can't figure out why that part
isn't working right.

The theory behind this macro is: unfilter two main sheets in
my workbook and unhide their hidden cols; sort to set a keyed
reference column to its proper order; go to the two normally hidden
sheets referenced from the two main sheets; unhide/unprotect them
and refresh their data queries; update ranges; rehide/re-protect;
go back to the two main sheets and update the row count to fit
the newly updated referenced data sheets; pull down formulas and
references; convert formulas to values for speed, in all but the
first row; re-sort to desired nominal view; re-auto-filter; set the
active cells to the bottom data rows (doesn't work right).

-dman-

------------------------ start of macro -------------------------

Option Explicit
Sub RGUpdate()
'
' Update Realized Gains and Restate Current Positions
' Macro by Dallman Ross w/ massive kibitzing by Dave Peterson
' Last edited 18-Nov-2006

'************************************************* ********
Dim wsRG As Worksheet
Dim wsUG As Worksheet
Dim csvRG As Worksheet
Dim csvUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant
'************************************************* ********

' Loop through regular worksheets

'************************************************* ********
wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
.Cells.EntireColumn.Hidden = False

If .FilterMode Then
.ShowAllData
End If

wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row
Debug.Print iCtr, wsLastRows(iCtr), wsLastCols(iCtr)

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))
End With

With wsRng(iCtr)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the first loop."

'************************************************* ********
Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")
Dim csvLastRows As Variant
'************************************************* ********

' Loop through csv source worksheets

'************************************************* ********
wsNames = Array(csvRG, csvUG)
ReDim csvLastRows(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

Application.DisplayAlerts = False
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Visible = True
.Activate
.Range("A1").Select 'set focus
.Unprotect

.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

csvLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
.Visible = False
End With
Next iCtr

Debug.Print "We're done with the second loop."

' Loop again through regular worksheets _
adding or deleting rows, as needed

'************************************************* ********
Dim AdjustRows As Long
wsNames = Array(wsRG, wsUG)
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
AdjustRows = csvLastRows(iCtr) - wsLastRows(iCtr)
Debug.Print AdjustRows

If AdjustRows Then

' Add or delete rows
If AdjustRows 0 Then
.Range(Rows(wsLastRows(iCtr) + 1), _
Rows(wsLastRows(iCtr)). _
Offset(AdjustRows, 0)).EntireRow.Insert
Else
.Range(Rows(wsLastRows(iCtr) + AdjustRows + 1), _
Rows(wsLastRows(iCtr)). _
Offset(0, 0)).EntireRow.Delete

End If

' Copy first data row
.Range("A2", .Cells(2, wsLastCols(iCtr))).Copy

' Paste to fill out sheet range; _
convert to values as of Row 3

.Range("A3", .Cells(csvLastRows(iCtr), _
wsLastCols(iCtr))).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If

.Range("A1", .Cells(wsLastRows(iCtr) + AdjustRows, _
wsLastCols(iCtr))).Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("J2"), Order2:=xlAscending, _
Key3:=Range("M2"), Order3:=xlDescending, _
header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the range-adjustment loop."

' Sort and pre-filter main Realized Gains sheet

With wsRG
.Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("P2"), Order2:=xlAscending, _
Key3:=Range("I2"), Order3:=xlAscending, _
header:=xlYes
Selection.AutoFilter Field:=6, Criteria1:="<-"
Selection.AutoFilter Field:=23, Criteria1:="<1000"
End With

'************************************************* ********
wsNames = Array(wsRG, wsUG)
'************************************************* ********

' Final loop through regular worksheets _
hiding rows, positioning cursor

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
Application.Run "HideCols"

'Go to bottom -- doesn't work right for some reason
.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select
End With
Next iCtr

Debug.Print "We're done with HideCols loop."

Application.DisplayAlerts = True
End Sub



--

Dave Peterson