Identify Hidden Columns - New issue!

[Steve]

Hi Guys,

I thought I'd start a new thread since the old was so huge! The code
you provided (Garry, Isabelle, Clif, Charabeuh - Thank you!!) worked
like a charm while I was using Excel 2003. I was just recently
upgraded to Excel 2010, and now the code freezes up (Excel Not
Responding). I stepped through the code, and it appears to me that
the issue lies with the unhiding of hidden columns in the source
sheet. I tried both code sets (distinctly different) with the same
result. Any ideas? Thanks!

Sub Copy_Rows()

Dim wksSource As Worksheet, wksTarget As Worksheet
Dim ColumnsList()
Dim x As Integer, i As Integer
Dim rgVisible As Range

'********************************************************************************
On Error Resume Next
Set columnfilter = Application.InputBox(prompt:= _
"Select Any Cell in Column to Filter On", Type:=8)
If columnfilter Is Nothing Then
blCancelled = True
Exit Sub
End If
On Error GoTo 0
'********************************************************************************

blCancelled = False

On Error Resume Next
Set wksSource = ActiveSheet
UserForm1.Show '//get wksTarget sheetname
Set wksTarget = Sheets(gsWksTargetName)
On Error GoTo 0

If blCancelled Then Exit Sub

'********************************************************************************

Application.ScreenUpdating = False

With wksTarget
.Rows("1:" & CStr(.UsedRange.Rows.Count)).ClearContents
.Rows("1:" & CStr(.UsedRange.Rows.Count)).ClearComments
.Rows("1:" & CStr(.UsedRange.Rows.Count)).Interior.ColorIndex =
xlNone
End With

'*************Unhide Columns Code***************
'1st code set to Unhide
'x = 0
'With wksSource
' For i = 1 To .Columns.Count
' If .Columns(i).Hidden Then
' ReDim Preserve ColumnsList(x): ColumnsList(x) = i
' .Columns(i).Hidden = False: x = x + 1
' End If
' Next i
'End With

'2nd code set to unhide
With wksSource
On Error Resume Next
Set rgVisible = .Rows("1:1").SpecialCells(xlCellTypeVisible)
.Columns.Hidden = False
On Error GoTo 0
End With

'*****************AutoFilter********************
With wksSource
columnfilter.EntireColumn.AutoFilter Field:=1, Criteria1:="Y"
.UsedRange.Copy
wksTarget.Range("1:1").PasteSpecial Paste:=xlPasteColumnWidths
.UsedRange.Copy wksTarget.Range("1:1") '//put the data
columnfilter.EntireColumn.AutoFilter
End With

'*************Hide Columns Code*****************
'1st code set to re hide
' For i = LBound(ColumnsList) To UBound(ColumnsList)
' wksSource.Columns(ColumnsList(i)).Hidden = True
' wksTarget.Columns(ColumnsList(i)).Hidden = True
' Next

'2nd code set to re hide
With wksSource
.Columns.Hidden = True
If Not rgVisible Is Nothing Then _
rgVisible.EntireColumn.Hidden = False
End With

Application.ScreenUpdating = True

End Sub

 

[GS]

I'm gonna suggest it's not hanging so much as it's got lots more cols
to loop through. If you limit the loop to the number cols in the
UsedRange then maybe the loop will finish sooner and the rest of your
code will continue more promptly.

 

[Steve]

Hey Garry,

I modified the Hide code to read:

With wksSource
On Error Resume Next
Set rgVisible
= .UsedRange.Rows("1:1").SpecialCells(xlCellTypeVisible)
.Columns.Hidden = False
On Error GoTo 0
End With

Same result.

 

[GS]

After serious thinking Steve wrote :

Hey Garry,

I modified the Hide code to read:

With wksSource
On Error Resume Next
Set rgVisible
= .UsedRange.Rows("1:1").SpecialCells(xlCellTypeVisible)
.Columns.Hidden = False
On Error GoTo 0
End With

Same result

That's not doing anything different. Try...

wksSource.Rows(1).Columns.Hidden = False

 

[Steve]

Looks like that did the trick. Thanks Garry!

After serious thinking Steve wrote :





That's not doing anything different. Try...

  wksSource.Rows(1).Columns.Hidden = False

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

 

[GS]

You're welcome, Steve. Always glad to help...

 

[Steve]

So I must be losing my mind. It seemed to work perfectly for a few
runs...now it "hangs up" again. I stepped through the code once more,
and now it sticks on the line
.UsedRange.Copy wksTarget.Range("1:1") '//put the data

With wksSource
.AutoFilterMode = False
columnfilter.EntireColumn.AutoFilter Field:=1, Criteria1:="Y"
.UsedRange.Copy
wksTarget.Range("1:1").PasteSpecial Paste:=xlPasteColumnWidths
.UsedRange.Copy wksTarget.Range("1:1") '//put the data
columnfilter.EntireColumn.AutoFilter
End With

I swore it worked fine before...any ideas?

 

[Steve]

So based on your last answer, I replaced .Range("1:1") with ,Rows(1)
and it works fine. What is the difference between the two??

 

[Steve]

SO confused now. I ran the code again, and it hung up. I shut down
excel, restarted and stepped through the code. I stepped through it
perfectly with no issues. I then ran the code and it hung up! Huh??

 

[GS]

Steve,
The original code still works as expected in my sample wkb. I'm not
able to reproduce the issue you claim and so I'm wondering if your file
has become corrupt. (Just guessing at this point)

 

[Steve]

My brain may have reached the "law of NEGATIVE" returns! I stepped
through the code, and I have a problem here (I'm sure I screwed
something up!):

.UsedRange.Copy
wksTarget.Rows(1).PasteSpecial
Paste:=xlPasteColumnWidths
.UsedRange.Copy wksTarget.Rows(1) '//put the data

As I step through, The usedrange.copy is working perfectly. After the
pastespecial line executes, I noticed the ENTIRE sheet (to column XFD)
is highlighted. So somehow it is pasting to a HUGE area.

 

[GS]

Steve pretended :

.UsedRange.Copy

wksTarget.Rows(1).PasteSpecial Paste:=xlPasteColumnWidths

.UsedRange.Copy wksTarget.Rows(1) '//put the data

The PasteSpecial line is all one line. Not sure why you had it on two
lines but you need to change it as above.

 

[Steve]

Hey Garry,

I do have it all on one line...must have wrapped text when I pasted it
to the message. I don't know what the problem is. I re-saved the
file as a new name, rebooted my pc, and it worked...for a while. It's
sporadic, so not sure if its an issue with how I have the code written
or a file corruption issue, or just something goofy with excel 2010.
Oh well

 

[GS]

I don't have 2010 installed anywhere yet so I can only speak to
versions 9 to 12. The code works as expected in all and so is why I
suggested your wkb may be corrupted, which explains the unexpected
behavior. Try it in earlier versions to see if the behavior shows
itself there. If so, rebuild it from scratch by exporting all modules
and importing them to a new wkb.

 

[Steve]

Good idea! Thanks Garry.

 

[Clif McIrvin]

Hi Guys,

I thought I'd start a new thread since the old was so huge! The code
you provided (Garry, Isabelle, Clif, Charabeuh - Thank you!!) worked
like a charm while I was using Excel 2003. I was just recently
upgraded to Excel 2010, and now the code freezes up (Excel Not
Responding). I stepped through the code, and it appears to me that
the issue lies with the unhiding of hidden columns in the source
sheet. I tried both code sets (distinctly different) with the same
result. Any ideas? Thanks!

Hi Steve

Something of a shot in the dark .... I've read hints that xl2010 doesn't
handle multi-core hardware as well as the development team intended it
to .... are you by chance running on a multi-core machine?

Under File | Options | Advanced | Formulas there is an option to disable
or configure multi-threaded processing. It might be worth experimenting
..... please report back if this seems to make any difference.

 

[Steve]

Hey Clif,

According to where you told me to go, it appears I have 4 processors.
If I disable, will that affect performance (speed)?

 

[Clif McIrvin]

Steve, if the problems go away what does speed matter? <grin>

(At least it "shouldn't" be any slower than "in the old days". -- But I
do see occasional reports that 2010 is slower than 2003.)

I have no experience with multi-core machines ... I have no idea.
Someone in this room is running xl2010 on multi-core machines -- whether
they're watching this thread or not is anybody's guess.

--
Clif

Hey Clif,

According to where you told me to go, it appears I have 4 processors.
If I disable, will that affect performance (speed)?

 

[GS]

Clif McIrvin brought next idea :

Hi Steve

Something of a shot in the dark .... I've read hints that xl2010 doesn't
handle multi-core hardware as well as the development team intended it to
.... are you by chance running on a multi-core machine?

Under File | Options | Advanced | Formulas there is an option to disable or
configure multi-threaded processing. It might be worth experimenting ....
please report back if this seems to make any difference.

Hi Clif,

<FWIW>
2007 has that same option and I haven't been able to dupe Steve's
problem with that option enabled. I still haven't installed 2010 and so
can't speak to that.

 

[Clif McIrvin]

Hi Guys,

I thought I'd start a new thread since the old was so huge! The code
you provided (Garry, Isabelle, Clif, Charabeuh - Thank you!!) worked
like a charm while I was using Excel 2003. I was just recently
upgraded to Excel 2010, and now the code freezes up (Excel Not
Responding). I stepped through the code, and it appears to me that
the issue lies with the unhiding of hidden columns in the source
sheet. I tried both code sets (distinctly different) with the same
result. Any ideas? Thanks!

Hi Steve

I copied your code into one of my xl2010 workbooks and it seems to run
just fine.

I modified it to get the criteria from the cell I select in your
application.inputbox to make it easy to try with different permutations
of my data -- I also hardcoded the wksTarget sheetname. Neither of these
changes should have had any impact.

I did notice that there were a couple undefined variables (I always run
with Option Explicit) - I don't know if that has any bearing on what you
are seeing or not: columnfilter and blCancelled.

My worksheet is about 2500 rows by 70 columns ... choice "A" copied 990
rows, choice "B" copied about 400 rows and choice "C" copied about 50
rows. I ran your macro several times without error.

Execution times varied between 2 and 7 seconds (1001, 1002, 1003, ...)

Not sure what this tells you.....