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

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!

Have you tried Selection.CurrentRegion.Select

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


"Corey" wrote:

> 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!

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.

"Kevin B" wrote:

>
> Have you tried Selection.CurrentRegion.Select
>
> it's the VBA equivalent of Ctrl + Shift + * or Ctrl + (Num Pad) *
> --
> Kevin Backmann
>
>
> "Corey" wrote:
>
> > 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!

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.
--
Gary''s Student


"Corey" wrote:

> 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.
>
> "Kevin B" wrote:
>
> >
> > Have you tried Selection.CurrentRegion.Select
> >
> > it's the VBA equivalent of Ctrl + Shift + * or Ctrl + (Num Pad) *
> > --
> > Kevin Backmann
> >
> >
> > "Corey" wrote:
> >
> > > 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!

Awesome Gary! Many thanks!!!

"Gary''s Student" wrote:

> 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.
> --
> Gary''s Student
>
>
> "Corey" wrote:
>
> > 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.
> >
> > "Kevin B" wrote:
> >
> > >
> > > Have you tried Selection.CurrentRegion.Select
> > >
> > > it's the VBA equivalent of Ctrl + Shift + * or Ctrl + (Num Pad) *
> > > --
> > > Kevin Backmann
> > >
> > >
> > > "Corey" wrote:
> > >
> > > > 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!

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

??

"Corey" wrote:

> Awesome Gary! Many thanks!!!
>
> "Gary''s Student" wrote:
>
> > 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.
> > --
> > Gary''s Student
> >
> >
> > "Corey" wrote:
> >
> > > 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.
> > >
> > > "Kevin B" wrote:
> > >
> > > >
> > > > Have you tried Selection.CurrentRegion.Select
> > > >
> > > > it's the VBA equivalent of Ctrl + Shift + * or Ctrl + (Num Pad) *
> > > > --
> > > > Kevin Backmann
> > > >
> > > >
> > > > "Corey" wrote:
> > > >
> > > > > 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!

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


"Corey" <[email protected]> wrote in message
news:[email protected]...
> Hmmm...I'm trying it now and I'm getting 'Ambiguous name detected: m2t' when
> I try to run it
>
> ??
>
> "Corey" wrote:
>
>> Awesome Gary! Many thanks!!!
>>
>> "Gary''s Student" wrote:
>>
>> > 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.
>> > --
>> > Gary''s Student
>> >
>> >
>> > "Corey" wrote:
>> >
>> > > 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.
>> > >
>> > > "Kevin B" wrote:
>> > >
>> > > >
>> > > > Have you tried Selection.CurrentRegion.Select
>> > > >
>> > > > it's the VBA equivalent of Ctrl + Shift + * or Ctrl + (Num Pad) *
>> > > > --
>> > > > Kevin Backmann
>> > > >
>> > > >
>> > > > "Corey" wrote:
>> > > >
>> > > > > 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!

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?

"Bernie Deitrick" wrote:

> 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
>
>
> "Corey" <[email protected]> wrote in message
> news:[email protected]...
> > Hmmm...I'm trying it now and I'm getting 'Ambiguous name detected: m2t' when
> > I try to run it
> >
> > ??
> >
> > "Corey" wrote:
> >
> >> Awesome Gary! Many thanks!!!
> >>
> >> "Gary''s Student" wrote:
> >>
> >> > 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.
> >> > --
> >> > Gary''s Student
> >> >
> >> >
> >> > "Corey" wrote:
> >> >
> >> > > 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.
> >> > >
> >> > > "Kevin B" wrote:
> >> > >
> >> > > >
> >> > > > Have you tried Selection.CurrentRegion.Select
> >> > > >
> >> > > > it's the VBA equivalent of Ctrl + Shift + * or Ctrl + (Num Pad) *
> >> > > > --
> >> > > > Kevin Backmann
> >> > > >
> >> > > >
> >> > > > "Corey" wrote:
> >> > > >
> >> > > > > 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!
>
>
>

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.
--
Gary's Student


"Corey" wrote:

> 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?
>
> "Bernie Deitrick" wrote:
>
> > 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
> >
> >
> > "Corey" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hmmm...I'm trying it now and I'm getting 'Ambiguous name detected: m2t' when
> > > I try to run it
> > >
> > > ??
> > >
> > > "Corey" wrote:
> > >
> > >> Awesome Gary! Many thanks!!!
> > >>
> > >> "Gary''s Student" wrote:
> > >>
> > >> > 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.
> > >> > --
> > >> > Gary''s Student
> > >> >
> > >> >
> > >> > "Corey" wrote:
> > >> >
> > >> > > 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.
> > >> > >
> > >> > > "Kevin B" wrote:
> > >> > >
> > >> > > >
> > >> > > > Have you tried Selection.CurrentRegion.Select
> > >> > > >
> > >> > > > it's the VBA equivalent of Ctrl + Shift + * or Ctrl + (Num Pad) *
> > >> > > > --
> > >> > > > Kevin Backmann
> > >> > > >
> > >> > > >
> > >> > > > "Corey" wrote:
> > >> > > >
> > >> > > > > 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!
> >
> >
> >

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

"Gary''s Student" wrote:

> 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.
> --
> Gary's Student
>
>
> "Corey" wrote:
>
> > 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?
> >
> > "Bernie Deitrick" wrote:
> >
> > > 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
> > >
> > >
> > > "Corey" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hmmm...I'm trying it now and I'm getting 'Ambiguous name detected: m2t' when
> > > > I try to run it
> > > >
> > > > ??
> > > >
> > > > "Corey" wrote:
> > > >
> > > >> Awesome Gary! Many thanks!!!
> > > >>
> > > >> "Gary''s Student" wrote:
> > > >>
> > > >> > 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.
> > > >> > --
> > > >> > Gary''s Student
> > > >> >
> > > >> >
> > > >> > "Corey" wrote:
> > > >> >
> > > >> > > 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.
> > > >> > >
> > > >> > > "Kevin B" wrote:
> > > >> > >
> > > >> > > >
> > > >> > > > Have you tried Selection.CurrentRegion.Select
> > > >> > > >
> > > >> > > > it's the VBA equivalent of Ctrl + Shift + * or Ctrl + (Num Pad) *
> > > >> > > > --
> > > >> > > > Kevin Backmann
> > > >> > > >
> > > >> > > >
> > > >> > > > "Corey" wrote:
> > > >> > > >
> > > >> > > > > 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!
> > >
> > >
> > >