Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select two consecutive rows
I know that if I want to select for example a whole column
of contiguous data, that I can select in this manner: dataRange(Selection, Selection.End(xlDown)).Select __________________________________________________ ________ However, if I only want to start with selecting two cells, then I ought to be able to issue a statement like this one to select the active cell, plus one additional cell right below. But this doesn't work. Any ideas how to change it? Range(Selection, Offset(1, 0)).Select __________________________________________________ _________ Also, someone has gone to sleep today on the posting of newsgroups on the Microsoft web site where I normally can get these responses pretty timely. And google always stays behind. So, it may be a while before I get your post if you post back a response today. I don't get to use my favorite method of access newsgroups, Outlook Express when I'm at work. Bruce |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select two consecutive rows
Activecell.Resize(2,1).Select
Range(ActiveCell,ActiveCell.Offset(1,0)).Select Regards, Tom Ogilvy "Bruce Roberson" wrote in message ... I know that if I want to select for example a whole column of contiguous data, that I can select in this manner: dataRange(Selection, Selection.End(xlDown)).Select __________________________________________________ ________ However, if I only want to start with selecting two cells, then I ought to be able to issue a statement like this one to select the active cell, plus one additional cell right below. But this doesn't work. Any ideas how to change it? Range(Selection, Offset(1, 0)).Select __________________________________________________ _________ Also, someone has gone to sleep today on the posting of newsgroups on the Microsoft web site where I normally can get these responses pretty timely. And google always stays behind. So, it may be a while before I get your post if you post back a response today. I don't get to use my favorite method of access newsgroups, Outlook Express when I'm at work. Bruce |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select two consecutive rows
Tom:
Actually what I'm running into is this: I'm sitting in Cell A10 which is the left most corner of an area I need to highlight for an advanced query setup with fields. I got there with the command below since I had been in Cell B10 when I made my copy of the range type2header: ActiveCell.Offset(0, -1).Select From Cell A10, I have contiguous data beginning in cells A11 and in Cell B10. From there it is contiguous down through lets say to cell S325. I also have a complication in that Cells B326 through H326 should not be highlighted as they are a different data type and not needed for the advanced query operation. Now these are not finite row numbers; they will always vary each month as to how far down it goes. Ordinarily I might have done a selection of the current region, but I guess you can see that won't work in this case. If I try and go end down end right with the commands below, it does not work to make the selection I need. Range(ActiveCell, ActiveCell.End(xlDown)).Select Range(ActiveCell, ActiveCell.End(xlToRight)).Select So, I'm stuck at this point on how to highlight what I need. Listed below is my full routine so far. __________________________________________________ __________ Sub Copytype2hdr() Range("startexportcell").Offset(1, 0).Select 'Selects B9 Selection.EntireRow.Insert 'Inserting one row Range("Type2header").Copy 'header for adv query ActiveCell.PasteSpecial xlPasteValues 'paste header in B10 Application.CutCopyMode = False ActiveCell.Offset(0, -1).Select move cursor to A10 Range(ActiveCell, ActiveCell.End(xlDown)).Select Range(ActiveCell, ActiveCell.End(xlToRight)).Select End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select two consecutive rows
Range(ActiveCell.End(xlDown),ActiveCell.End(xlToRi ght)).Select
Regards, Tom Ogilvy Bruce Roberson wrote in message ... Tom: Actually what I'm running into is this: I'm sitting in Cell A10 which is the left most corner of an area I need to highlight for an advanced query setup with fields. I got there with the command below since I had been in Cell B10 when I made my copy of the range type2header: ActiveCell.Offset(0, -1).Select From Cell A10, I have contiguous data beginning in cells A11 and in Cell B10. From there it is contiguous down through lets say to cell S325. I also have a complication in that Cells B326 through H326 should not be highlighted as they are a different data type and not needed for the advanced query operation. Now these are not finite row numbers; they will always vary each month as to how far down it goes. Ordinarily I might have done a selection of the current region, but I guess you can see that won't work in this case. If I try and go end down end right with the commands below, it does not work to make the selection I need. Range(ActiveCell, ActiveCell.End(xlDown)).Select Range(ActiveCell, ActiveCell.End(xlToRight)).Select So, I'm stuck at this point on how to highlight what I need. Listed below is my full routine so far. __________________________________________________ __________ Sub Copytype2hdr() Range("startexportcell").Offset(1, 0).Select 'Selects B9 Selection.EntireRow.Insert 'Inserting one row Range("Type2header").Copy 'header for adv query ActiveCell.PasteSpecial xlPasteValues 'paste header in B10 Application.CutCopyMode = False ActiveCell.Offset(0, -1).Select move cursor to A10 Range(ActiveCell, ActiveCell.End(xlDown)).Select Range(ActiveCell, ActiveCell.End(xlToRight)).Select End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select two consecutive rows
I'll try this in the morning when I get to work, but is this going to work
if the active cell is blank, and the cell to the right and the cell below are not blank? Bruce "Tom Ogilvy" wrote in message ... Range(ActiveCell.End(xlDown),ActiveCell.End(xlToRi ght)).Select Regards, Tom Ogilvy Bruce Roberson wrote in message ... Tom: Actually what I'm running into is this: I'm sitting in Cell A10 which is the left most corner of an area I need to highlight for an advanced query setup with fields. I got there with the command below since I had been in Cell B10 when I made my copy of the range type2header: ActiveCell.Offset(0, -1).Select From Cell A10, I have contiguous data beginning in cells A11 and in Cell B10. From there it is contiguous down through lets say to cell S325. I also have a complication in that Cells B326 through H326 should not be highlighted as they are a different data type and not needed for the advanced query operation. Now these are not finite row numbers; they will always vary each month as to how far down it goes. Ordinarily I might have done a selection of the current region, but I guess you can see that won't work in this case. If I try and go end down end right with the commands below, it does not work to make the selection I need. Range(ActiveCell, ActiveCell.End(xlDown)).Select Range(ActiveCell, ActiveCell.End(xlToRight)).Select So, I'm stuck at this point on how to highlight what I need. Listed below is my full routine so far. __________________________________________________ __________ Sub Copytype2hdr() Range("startexportcell").Offset(1, 0).Select 'Selects B9 Selection.EntireRow.Insert 'Inserting one row Range("Type2header").Copy 'header for adv query ActiveCell.PasteSpecial xlPasteValues 'paste header in B10 Application.CutCopyMode = False ActiveCell.Offset(0, -1).Select move cursor to A10 Range(ActiveCell, ActiveCell.End(xlDown)).Select Range(ActiveCell, ActiveCell.End(xlToRight)).Select End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select two consecutive rows
Tom:
In your post yesterday, you listed this as a way to do the highlighting. Range(ActiveCell.End(xlDown),ActiveCell.End(xlTo Right)).Select Since Cell A10 is the left most cell and since it is blank, this line of code only highlighted Cells A10..B11 because of the starting cell. Is there a way to first select this cell and then still do an end down, end right type code?Again it appears you only get two select command lines in a row and then the thing resets itself for some reason. I think the whole problem is that the left most cell in the range is where the focus of the end to right and the end down type commands move from. Bruce *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select two consecutive rows
Ok, I got the full routine done. I had to give the range "type2header" a
dummy entry in Column A, so that when it was copied it would take the selection that Tom had given me yesterday. "Range(ActiveCell.End(xlDown), ActiveCell.End _(xlToRight)).Select" Other than that workaround, it was just a matter of hiding the zero lines in the data with the advanced filter so I could copy what was left over to a new spreadsheet and then save that in the CSV format required by the state for the upload to their system. *****Piece of Cake Right?**** <vbg There is one thing I'd rather do that I haven't tried yet. I'd rather the csv file name be named in part by the spreadhsheet range value found in range PRMO. So, rather than: ActiveWorkbook.SaveAs Filename:= _ "D:\MyFiles\Data\nmtax\NMTAX.CSV", etc etc I'd rather it be: ActiveWorkbook.SaveAs Filename:= _ "D:\MyFiles\Data\nmtax\"Prmo"&NMTAX.CSV", notice the "prmo"& added in there. Is there a way to make that sort of thing work here? My complete procedure is listed below for your review: Sub Taxcsvfile() Range("startexportcell").Offset(1, -1).Select Selection.EntireRow.Insert Range("Type2header").Copy ActiveCell.PasteSpecial xlPasteValues Application.CutCopyMode = False Range(ActiveCell.End(xlDown), ActiveCell.End(xlToRight)).Select Selection.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("ExportCriti"), Unique:=False Range("StartExportCell").Offset(1, 0).Select Selection.EntireRow.Delete Range("StartExportCell").Select Range(ActiveCell.End(xlDown), ActiveCell.Offset(0, 20)).Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Application.CutCopyMode = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "D:\MyFiles\Data\nmtax\NMTAX.CSV", FileFormat:=xlCSV _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close Sheets("ExportTax").ShowAllData Range("A1").Select Application.DisplayAlerts = True End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select two consecutive rows
Range(ActiveCell.Offset(1,0).End(xlDown), _
ActiveCell.Offset(0,1).End(xlToRight)).Select Regards, Tom Ogilvy Bruce Roberson wrote in message ... Tom: In your post yesterday, you listed this as a way to do the highlighting. Range(ActiveCell.End(xlDown),ActiveCell.End(xlTo Right)).Select Since Cell A10 is the left most cell and since it is blank, this line of code only highlighted Cells A10..B11 because of the starting cell. Is there a way to first select this cell and then still do an end down, end right type code?Again it appears you only get two select command lines in a row and then the thing resets itself for some reason. I think the whole problem is that the left most cell in the range is where the focus of the end to right and the end down type commands move from. Bruce *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula from consecutive rows to alternate rows? | Excel Discussion (Misc queries) | |||
Transposing data from consecutive rows into non-consecutive rows | Excel Discussion (Misc queries) | |||
min function on non consecutive rows | Excel Worksheet Functions | |||
PULLING OUT CONSECUTIVE ROWS | Excel Discussion (Misc queries) | |||
Select non-consecutive rows | Excel Discussion (Misc queries) |