How to find first and last element in a column

earp_

Active Member
Joined
Apr 30, 2008
Messages
305
Does anyone know how to find the last value and the first in a column with vba?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
find the first value..

Code:
X = 10 'column # to look in..
If Len(Cells(1,X)) = 0 Then
    FirstUsedRow = 1
Else
    FirstUsedRow = Cells(1,X).End(xldown).Row
End If
 
MsgBox Cells(FirstUsedRow, X).Value

Find Last Value

Code:
X = 10 ' Column # to look in.
LastUsedRow = Cells(Rows.Count, X).End(xlup).Row
MsgBox Cells(LastUsedRow, X).Value

Hope This Helps
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Actually, you may have a problem with this line
If Len(Cells(1,X)) = 0 Then

IF the cell in Row 1 is a formula resulting in "", it will think that cell is NOT used...

maybe replace that line with

If Len(Cells(1,X).Formula) = 0 Then
 
Upvote 0

earp_

Active Member
Joined
Apr 30, 2008
Messages
305
Do you know how can I find it also with a method similar to this?
Worksheets("MaxMin").Range("B65536").End(xlUp).Offset(1, 0) = Application.WorksheetFunction.Max(.Range(.Range("A65536").End(xlUp), .Range("A65536").End(xlUp).Offset(-5)))
Every 5 seconds I find the max of the last 5 cells in a column.
Do you know how to find the last and the first value for an offset(-5) ?
So that at the end I have the first elem, the last elem and the max each 5 values.
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
You have your own answer inside the code you just posted...

Post back again in a few minutes if you can't find it...
 
Upvote 0

Forum statistics

Threads
1,191,689
Messages
5,988,031
Members
440,124
Latest member
vincentchu2369

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top