VSB code - 'Home' & 'CTRL+End'

C

Corey

I'm trying to write a macro for formatting a .txt file into an Excel
spreadsheet. It's different everymonth. I can get all the keystrokes recorded
correctly except for the following:

Let's say a portion of the macro takes me to cell A1681, I would then like
to highlight that row and all the rows above it (Ctrl+Shift+Home). However,
it only references the count of rows between that and row 1, which will vary
each time. Is there a code for this. Also, the opposite, if I'd rather
highlight row A1681 and all the rows to the bottom of the spreadsheet.

Any help is much appreciated. Thanks!
 
K

Kevin B

Have you tried Selection.CurrentRegion.Select

it's the VBA equivalent of Ctrl + Shift + * or Ctrl + (Num Pad) *
 
C

Corey

This seems to highlight cells above and below. I only want to either (1)
highlight everything below the current cell position or (2) highlight
everything above the current cell position. Thanks.
 
G

Gary''s Student

Hi Corey:

Let's say you have selected any cell. Then

Sub m2t()
Dim i As Range
Dim j As Range
Set j = Cells(1, 1)
Set i = Selection
Range(i, j).EntireRow.Select
End Sub

Will select everything thru your selection to row #1.

if you use Cells(65536,1) you will get the other sector.
 
C

Corey

Awesome Gary! Many thanks!!!

Gary''s Student said:
Hi Corey:

Let's say you have selected any cell. Then

Sub m2t()
Dim i As Range
Dim j As Range
Set j = Cells(1, 1)
Set i = Selection
Range(i, j).EntireRow.Select
End Sub

Will select everything thru your selection to row #1.

if you use Cells(65536,1) you will get the other sector.
Click to expand...
 
C

Corey

Hmmm...I'm trying it now and I'm getting 'Ambiguous name detected: m2t' when
I try to run it

??
 
B

Bernie Deitrick

Corey,

You have multiple copies of that macro in the module - you can only have one - delete the others or
comment them out.

Sub m2t()
....
End Sub

HTH,
Bernie
MS Excel MVP
 
C

Corey

Doesn't seem to be working. I need to use some code to do the same type of
thing three times. It needs to search column A for "1", then delete all rows
above that, then search A for "a/" and delete that row and everything below
it. Then sort by column C, go to the last empty cell of that column and
delete everything below that. Are you saying that the code can only be used
once in the macro?
 
G

Gary''s Student

Corey:

The routine, as coded, assumes that you ( or another piece of VBA code) has
Selected a cell or cells in some row. All the routine does is to extend the
Selection upwards back to row #1.

Make another copy of the code with a different name (say m3t) and
Cells(65536,1) in place of Cells(1,1) to expand the Selection from the
cell(s) Selected when the routine is called to that row thru the end of the
worksheet.



What will happen is if you have Selected A100 and then call m2t, then rows
1 thru 100 will be Selected.

If you Select A100 and call m3t, then rows 100 thru 65536 will be Selected.


In any case, if you continue to have questions, update this post and we will
continue to work at it.
 
C

Corey

Perfect!!!! I was unaware of the different naming functions. Thanks a bunch
you guys!!!
 
Top