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