Macro basics

[Dave Peterson]

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).

[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:

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).

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:

[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

 

[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"

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


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

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:

[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]

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>>

 

[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?

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

 

[Dallman Ross]

[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]

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.

[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

 

[Dallman Ross]

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-

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

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]

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.

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-

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

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

 

[Dallman Ross]

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

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

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]

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.

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

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

In <[email protected]>, Dave Peterson
<[email protected]> 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