Excel Row Select in VBA

[rnordquest]

This works by hand and is the same as the macro recorder but it doesn't work when I try to use it.

Range("EG3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select

The rows are filtered and the first group of rows is blank which is what prompted the double range select; it selects just the blanks including the first non-blank cell it comes to. I can hard code a fixed range but I'd prefer something more elegant which is where you guys come in.

 

[joerd]

Funny behavior, that's for sure. A quick solution is to work it backwards:

    Range("EG3").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Range(Selection, Range("EG3")).Select

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[rnordquest]

Thanks.

I have another ? When the macro closes a file I get a "Do you want to keep the clipboard stuff" decision window. How do I get the code to answer that? I don't want to pick up the mouse to click no.

 

[joerd]

Try
Application.CutCopyMode = False
at the end of the macro. This is a good habit anyway.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[rnordquest]

It didn't work at the end so I put it just before each close workbook line and it worked.

Many thanks,

Roger