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