unselecting or uncopying or whatever in VBA

[Guest]

I'm reasonably proficient in VB6 and Access VBA but am an Excel neophyte so
hopefully you can help me. Here's the scenario:
I do this to unhide some columns:
shtBDA.Columns("A:B").Select
If Selection.EntireColumn.Hidden = True Then
Selection.EntireColumn.Hidden = False
blRehideColumns = True
End If

then I do a bunch of stuff including this sort of thing:
shtBDA.Rows(lngBDAInd).Insert 'insert a new row
shtBDA.Rows(lngBDA_START_ROW).Copy
shtBDA.Rows(lngBDAInd).PasteSpecial

after all that whole bunch of stuff is done I'd like to re-hide the columns
like this:
shtBDA.Columns("A:B").Select
If blRehideColumns Then
Selection.EntireColumn.Hidden = True
End If

but the .Select fails (I reckon) because the Rows...copy has that row
selected. I believe this becauseIt doesn't fail if I Debug and stop
processing, switch back to the sheet and hit escape to deselect the row and
then continue processing.

How can I get that selection cleared from the Rows...copy statement?

Cheers,
Jon

 

[Guest]

Application.CutCopyMode = False

Insert this line when you finish your paste actions.

 

[Jim Cone]

re: "switch back to the sheet and hit escape "

You cannot select "anything" on a sheet unless that sheet is active.
The good news is that you don't have to select the sheet or the columns
to hide/unhide the columns...

shtBDA.Columns("A:B").Hidden = False

Also, it is a little more efficient to just hide or unhide the columns
without checking their status first.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

(I've been to Canby, OR)


"Jon in Canby Or." <[email protected]>
wrote in message
I'm reasonably proficient in VB6 and Access VBA but am an Excel neophyte so
hopefully you can help me. Here's the scenario:
I do this to unhide some columns:
shtBDA.Columns("A:B").Select
If Selection.EntireColumn.Hidden = True Then
Selection.EntireColumn.Hidden = False
blRehideColumns = True
End If

then I do a bunch of stuff including this sort of thing:
shtBDA.Rows(lngBDAInd).Insert 'insert a new row
shtBDA.Rows(lngBDA_START_ROW).Copy
shtBDA.Rows(lngBDAInd).PasteSpecial

after all that whole bunch of stuff is done I'd like to re-hide the columns
like this:
shtBDA.Columns("A:B").Select
If blRehideColumns Then
Selection.EntireColumn.Hidden = True
End If

but the .Select fails (I reckon) because the Rows...copy has that row
selected. I believe this becauseIt doesn't fail if I Debug and stop
processing, switch back to the sheet and hit escape to deselect the row and
then continue processing.
How can I get that selection cleared from the Rows...copy statement?
Cheers,
Jon

 

[Tom Ogilvy]

' note this only checks column A as hidden
If shtBDA.Columns("A:B").EntireColumn.Hidden = True Then
shtBDA.Columns("A:B").EntireColumn.Hidden = False
blRehideColumns = True
End If


shtBDA.Rows(lngBDAInd).Insert 'insert a new row
shtBDA.Rows(lngBDA_START_ROW).Copy
shtBDA.Rows(lngBDAInd).PasteSpecial

If blRehideColumns Then
shtBDA.Columns("A:B").EntireColumn.Hidden = True
End If

 

[Guest]

It sounds like you are asking for Application.CutCopyMode = False (which is
the VBA equivalent to hitting the ESC key after a copy/paste operation), but
I couldn't reproduce the error you are getting.

Note that it is usually never necessary to select things to work with them
(it's also faster to not select them). Also, it avoids errors (for example -
VBA is capable of copying data from one sheet to a hidden sheet, but not if
you try to select the hidden sheet before pasting the data).

Usually you can combine your code on one line by deleting the ".Select" on
the first line and the "Selection" on the next line and merge the two lines
into one (a simplified starting point for getting away from selecting things).

The With statement is also useful when you have several lines that refer to
the same object.

For example (and it may not be necessary to hide/unhide Columns A-B):

With shtBDA.Columns("A:B").
If .EntireColumn.Hidden = True Then
.EntireColumn.Hidden = False
blRehideColumns = True
End If
End With

With shtBDA
.Rows(lngBDAInd).Insert 'insert a new row
.Rows(lngBDA_START_ROW).Copy .Rows(lngBDAInd)
If blRehideColumns Then _
.Columns("A:B").EntireColumn.Hidden = True
End If

 

[Tom Ogilvy]

Also, it is a little more efficient to just hide or unhide the columns

without checking their status first.

My initial thought as well, but then realized he was testing to see how to
restore them at the end.