Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Select All Rows Except First?
I guess I want to concoct a .Range and then operate on it.
But, short of iterating thorugh rows to find the last row, I can't figure out how to do it. Googled a little, and "ActiveCell.SpecialCells(xlLastCell) seems to have the right ring to it. True? Either way, can somebody shed some light? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Select All Rows Except First?
Hi,
here's a few ways Lastrow1 = Cells(Cells.Rows.Count, "A").End(xlUp).Row Lastrow2 = ActiveSheet.UsedRange.Rows.Count LastRow3 = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Mike "PeteCresswell" wrote: I guess I want to concoct a .Range and then operate on it. But, short of iterating thorugh rows to find the last row, I can't figure out how to do it. Googled a little, and "ActiveCell.SpecialCells(xlLastCell) seems to have the right ring to it. True? Either way, can somebody shed some light? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Select All Rows Except First?
Actually, lastrow2 may give the wrong results.
If the .usedrange didn't start in A1 (or row 1), you could get the wrong answer. If the only cell that was used was z99, then the usedrange.rows.count would be equal to 1. You could use: dim LastRow2 as long with activesheet.usedrange lastrow2 = .rows(.rows.count).row end with ========= To the original poster: You can do the same thing with an arbitrary range (single area???): Dim myRng as range dim LastRow as long set myrng = activesheet.range("x9:z32") with myrng lastrow = .rows(.rows.count).row end with msgbox myrng.row & vblf & lastrow Mike H wrote: Hi, here's a few ways Lastrow1 = Cells(Cells.Rows.Count, "A").End(xlUp).Row Lastrow2 = ActiveSheet.UsedRange.Rows.Count LastRow3 = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Mike "PeteCresswell" wrote: I guess I want to concoct a .Range and then operate on it. But, short of iterating thorugh rows to find the last row, I can't figure out how to do it. Googled a little, and "ActiveCell.SpecialCells(xlLastCell) seems to have the right ring to it. True? Either way, can somebody shed some light? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Select All Rows Except First?
Option 3 is probably the best one to use when a user does not know the
parameters of their used range. Option 1 is OK if you are only working on a specific column as your criteria range. Option 2 is scary since the used range could begin in row 10 (or any row other than 1) and the integer returned would be that many rows short of the actual last row. "Mike H" wrote in message ... Hi, here's a few ways Lastrow1 = Cells(Cells.Rows.Count, "A").End(xlUp).Row Lastrow2 = ActiveSheet.UsedRange.Rows.Count LastRow3 = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Mike "PeteCresswell" wrote: I guess I want to concoct a .Range and then operate on it. But, short of iterating thorugh rows to find the last row, I can't figure out how to do it. Googled a little, and "ActiveCell.SpecialCells(xlLastCell) seems to have the right ring to it. True? Either way, can somebody shed some light? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Select All Rows Except First?
Thanks Mike, Dave, JLG.
I went with option in spite of JLGWhiz's observation. Reasoning: I'm *always* starting with row 2. Here's the code I wound up with (MS Access VBA): =========================================== Private Function excel_GenericExport_Finalize( _ ByVal theXlsPath As String, _ ByRef theReport As Report, _ ByRef theSS As Excel.Application _ ) As Boolean 22000 DebugStackPush mModuleName & ": excel_GenericExport_Finalize" 22001 On Error GoTo excel_GenericExport_Finalize_err ' PURPOSE: To provide one-stop shopping for the two ...GenericExport... routines ' when they put the finishing touches on the .XLS they create ' ACCEPTS: - Fully-qualified path to the spreadsheet in question ' - Pointer to the report the calling routine is working on ' - Pointer that we set to the .XLS so the calling routine can ' operate on the document if it wants to ' RETURNS: True as long as nothing abended 22002 Dim myWS As Excel.Worksheet Dim lastRow As Long 22010 If SpreadSheetOpen_Existing(theXlsPath, theSS) = True Then ' -------------------------- ' Remove any default sheets that the user's Excel defaults may have created 22011 If defaultSheets_Remove(theSS) = True Then 22012 Set myWS = theSS.Workbooks(1).Worksheets(1) 22013 With myWS ' -------------------------- ' Name only remaining tab per calling report's .Caption 22019 .Name = WorkSheetName_Legal(theReport.Caption, theSS.Workbooks(1)) ' -------------------------- ' Set all cells except header row's to Courier 10 22020 lastRow = .UsedRange.Rows.Count 22030 With .Rows("2:" & lastRow).Font 22031 .Name = "Courier New" 22032 .Size = 10 22039 End With ' -------------------------- ' Make header row bold 22040 .Rows(1).Font.Bold = True ' -------------------------- ' Insert a row at the top of the sheet and populate .Cell (1,1) ' with the contents of what the report's header text would have been ' NB: Here's where the convention requiring a .txtReportHeader in every ' report comes in. 22050 .Rows(1).Insert Shift:=xlDown 22060 With .Cells(1, 1) 22061 .Value = Eval(Right$(theReport! txtReportHeader.ControlSource, Len(theReport! txtReportHeader.ControlSource) - 1)) 22062 .Font.Bold = True 22063 .Font.Size = 16 22069 End With ' -------------------------- ' Save .XLS so user does not get prompted when they close it 22090 .Parent.save 22099 End With 22990 excel_GenericExport_Finalize = True 22991 End If 22999 End If excel_GenericExport_Finalize_xit: DebugStackPop On Error Resume Next Set myWS = Nothing Exit Function excel_GenericExport_Finalize_err: BugAlert True, "" Resume excel_GenericExport_Finalize_xit End Function =========================================== |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Select All Rows Except First?
Ok.
But I'm not sure why the safer code is more difficult: with .usedrange 22020 lastRow = .rows(.Rows.Count).row end with But it's your choice. PeteCresswell wrote: Thanks Mike, Dave, JLG. I went with option in spite of JLGWhiz's observation. Reasoning: I'm *always* starting with row 2. Here's the code I wound up with (MS Access VBA): =========================================== Private Function excel_GenericExport_Finalize( _ ByVal theXlsPath As String, _ ByRef theReport As Report, _ ByRef theSS As Excel.Application _ ) As Boolean 22000 DebugStackPush mModuleName & ": excel_GenericExport_Finalize" 22001 On Error GoTo excel_GenericExport_Finalize_err ' PURPOSE: To provide one-stop shopping for the two ...GenericExport... routines ' when they put the finishing touches on the .XLS they create ' ACCEPTS: - Fully-qualified path to the spreadsheet in question ' - Pointer to the report the calling routine is working on ' - Pointer that we set to the .XLS so the calling routine can ' operate on the document if it wants to ' RETURNS: True as long as nothing abended 22002 Dim myWS As Excel.Worksheet Dim lastRow As Long 22010 If SpreadSheetOpen_Existing(theXlsPath, theSS) = True Then ' -------------------------- ' Remove any default sheets that the user's Excel defaults may have created 22011 If defaultSheets_Remove(theSS) = True Then 22012 Set myWS = theSS.Workbooks(1).Worksheets(1) 22013 With myWS ' -------------------------- ' Name only remaining tab per calling report's .Caption 22019 .Name = WorkSheetName_Legal(theReport.Caption, theSS.Workbooks(1)) ' -------------------------- ' Set all cells except header row's to Courier 10 22020 lastRow = .UsedRange.Rows.Count 22030 With .Rows("2:" & lastRow).Font 22031 .Name = "Courier New" 22032 .Size = 10 22039 End With ' -------------------------- ' Make header row bold 22040 .Rows(1).Font.Bold = True ' -------------------------- ' Insert a row at the top of the sheet and populate .Cell (1,1) ' with the contents of what the report's header text would have been ' NB: Here's where the convention requiring a .txtReportHeader in every ' report comes in. 22050 .Rows(1).Insert Shift:=xlDown 22060 With .Cells(1, 1) 22061 .Value = Eval(Right$(theReport! txtReportHeader.ControlSource, Len(theReport! txtReportHeader.ControlSource) - 1)) 22062 .Font.Bold = True 22063 .Font.Size = 16 22069 End With ' -------------------------- ' Save .XLS so user does not get prompted when they close it 22090 .Parent.save 22099 End With 22990 excel_GenericExport_Finalize = True 22991 End If 22999 End If excel_GenericExport_Finalize_xit: DebugStackPop On Error Resume Next Set myWS = Nothing Exit Function excel_GenericExport_Finalize_err: BugAlert True, "" Resume excel_GenericExport_Finalize_xit End Function =========================================== -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Select All Rows Except First?
On Sep 28, 3:17*pm, Dave Peterson wrote:
Ok. But I'm not sure why the safer code is more difficult: I'm looking at it the day after and I'm not sure either.... -) TIme for a re-think. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i randomly select 780 rows from 4000 rows of data | Excel Worksheet Functions | |||
Concise Way To Tell If Range Contains Any Data? | Excel Programming | |||
More Concise Way To Assign Borders? | Excel Programming | |||
Make instructions clear, concise and in plain english. | Excel Discussion (Misc queries) | |||
Concise border removal method | Excel Programming |