Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VSB code - 'Home' & 'CTRL+End'
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VSB code - 'Home' & 'CTRL+End'
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VSB code - 'Home' & 'CTRL+End'
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VSB code - 'Home' & 'CTRL+End'
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VSB code - 'Home' & 'CTRL+End'
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VSB code - 'Home' & 'CTRL+End'
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" wrote in message ... 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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VSB code - 'Home' & 'CTRL+End'
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" wrote in message ... 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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
VSB code - 'Home' & 'CTRL+End'
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" wrote in message ... 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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
VSB code - 'Home' & 'CTRL+End'
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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated If Then / V Lookup / Match Statement... | Excel Worksheet Functions | |||
Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Find the cell value in excel by using vb code | Excel Discussion (Misc queries) | |||
close form code | Excel Discussion (Misc queries) |