Finding the first row on a spreadsheet
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Finding the first row on a spreadsheet
What would be the code to find the first row on a spreadsheet?
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding the first row on a spreadsheet
I assume that you mean the first non-blank row.
Code: Select all
Sub FirstRow()
Dim r As Long
r = ActiveSheet.UsedRange.Row
Debug.Print r
End Sub
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Finding the first row on a spreadsheet
Found row 1 which has headers and then several, 5 maybe blank rows then data begins, BUT there are formulas in row 3 and row 4, that should be ignored because the beginning of data is the row I was seeking. data begins in column A through column O.HansV wrote: ↑20 Jun 2023, 19:06I assume that you mean the first non-blank row.
Code: Select all
Sub FirstRow() Dim r As Long r = ActiveSheet.UsedRange.Row Debug.Print r End Sub
The formulas are in column B and K.
You assume correctly.
Thanks
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding the first row on a spreadsheet
That wasn't what you asked in your first question!
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Finding the first row on a spreadsheet
I realize my error.
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
Re: Finding the first row on a spreadsheet
If you structure the worksheet well, your question is redundant.
-
- Lounger
- Posts: 43
- Joined: 07 Jun 2023, 15:34
Re: Finding the first row on a spreadsheet
Try this:
Code: Select all
Sub test()
Dim wb As Workbook, ws As Worksheet, FirstRow As Long
Set wb = Application.ThisWorkbook
Set ws = wb.ActiveSheet
With ws
'http://www.siddharthrout.com/index.php/2018/02/10/find-last-row-and-last-column/
'Siddharth Rout, Feb 2018
FirstRow = .Range("A2:A10000").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
End With
Debug.Print FirstRow
End Sub
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Finding the first row on a spreadsheet
That works, thanksrobertocm wrote: ↑21 Jun 2023, 09:25Try this:Code: Select all
Sub test() Dim wb As Workbook, ws As Worksheet, FirstRow As Long Set wb = Application.ThisWorkbook Set ws = wb.ActiveSheet With ws 'http://www.siddharthrout.com/index.php/2018/02/10/find-last-row-and-last-column/ 'Siddharth Rout, Feb 2018 FirstRow = .Range("A2:A10000").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row End With Debug.Print FirstRow End Sub