Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 22nd, 2005, 05:36 AM
Friend of Wrox
Join Date: Jan 2005
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default find the FIRST USED row/column?

Last_Col = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
Last_Row = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

           i am using this code to find the LAST USED row/column,
            is there code to find the FIRST USED row/column?



Ayrshire Minis - a Mini E-Community
Old December 25th, 2005, 03:43 AM
Authorized User
Join Date: Feb 2005
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts


I never used this code, but this doesn't seem like a hard problem, so i tested this:

Last_Col = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlNext).Column
Last_Row = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlNext).Row

Just change xlPrevious into xlNext, now it will find the first cell used.


Old December 30th, 2005, 02:21 AM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts


You can use the specialcells method to get the last row

lLastRow = ActiveSheet.cells.specialcells(xlcelltypelastcell).row

Sadly, there is no xlcelltypefirstcell in Excel:(

However, use the usedrange count to get the range

lUsedRow = ActiveSheet.usedrange.rows.count

If lLastRow = LUsedRow then
  lFirstRow = 1
    lFirstRow = lLastRow - LUsedRow + 1
End if


Old July 23rd, 2013, 12:52 PM
Registered User
Join Date: Jul 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

This bit of code finds the first cell as long as it is within the first 500 cells. It is written to look in column A, but can look in whichever column of course.

Sub findfirst()

Dim x As Integer
x = 0

For i = 1 To 500

    If Not Cells(i, 1) = "" Then
    MsgBox "First Cell is: " & i
    x = 1
    End If
    If x = 1 Then
    GoTo lastline
    End If
Next i

End Sub

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find a Row in Datatable everest C# 1 August 1st, 2007 03:01 PM
Column to Row [email protected] Excel VBA 2 March 23rd, 2007 04:16 AM
Column to Row [email protected] SQL Server DTS 0 March 1st, 2007 10:57 AM
How to get the last row and last column value ramk_1978 SQL Server 2000 1 April 4th, 2005 06:34 PM
Read a column and Search Row by Row in another col AyatKh Excel VBA 2 January 26th, 2005 12:02 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.