Home > Uncategorized > Excel & VBa: find last row, column, cell in an Excel (work)sheet

Excel & VBa: find last row, column, cell in an Excel (work)sheet

Please visit the following site for the original Article.

http://www.nijbo.net/index.php?option=com_content&task=view&id=14&Itemid=32&mosmsg=Thanks+for+your+vote!

I found it extremely useful, hence, decided to copy it here for future reference.

 

User Rating: / 7 
PoorBest 

The following page contains some usefull (general) vba code that can be used to find the last row, columnand/or cell in an Excel (work)sheet.
Find the last used cell, before a blank in a Column:
Sub LastCellBeforeBlankInColumn()
  Range("A1").End(xldown).Select
End Sub 

Find the very last used cell in a Column:
Sub LastCellInColumn()
  Range("A65536").End(xlup).Select
End Sub

 
Find the very last used cell in a Column: 
Sub LastCellInColumn()
  Range("A65536").End(xlup).Select
End Sub

Find the last cell, before a blank in a Row: 
Sub LastCellBeforeBlankInRow()
  Range("A1").End(xlToRight).Select
End Sub

Find the very last used cell in a Row: 
Sub LastCellInRow()
  Range("IV1").End(xlToLeft).Select
End Sub

Find the very last used cell on a Worksheet: 
Sub Demo()
  Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
End Sub
 
Find the last used Row on a Worksheet:
Sub FindLastRow()
  Dim LastRow As Long

  If WorksheetFunction.CountA(Cells) > 0 Then
    ‘Search for any entry, by searching backwards by Rows.
    LastRow = Cells.Find(What:="*", After:=[A1], _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious).Row
    MsgBox LastRow
  End If
End Sub

Find the last used Column on a Worksheet:
Sub FindLastColumn()
  Dim LastColumn As Integer
  If WorksheetFunction.CountA(Cells) > 0 Then
    ‘Search for any entry, by searching backwards by Columns.
    LastColumn = Cells.Find(What:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
    MsgBox LastColumn
  End If
End Sub

 
Find the last used Cell on a Worksheet:
Private Sub FindLastCell()
  Dim LastColumn As Integer
  Dim LastRow As Long
  Dim LastCell As Range
  If WorksheetFunction.CountA(Cells) > 0 Then
    ‘Search for any entry, by searching backwards by Rows.
    LastRow = Cells.Find(What:="*", After:=[A1], _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious).Row
    ‘Search for any entry, by searching backwards by Columns.
    LastColumn = Cells.Find(What:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
    MsgBox Cells(LastRow, LastColumn).Address
  End If
End Sub 
 
Add a row at each change in a column.
Assume you have a long list of data and you want to insert a row at each change. While you could use a simple Loop this method is much faster. The Data must be sorted!
 
Option Explicit

Private Sub InsertRowAtEachChange()
  Dim objRange As Excel.Range

  ”’ On error goto the error handler defined in the lower part of this function.
  On Error GoTo ErrHandler

  ”’ Ensure an entire Column is selected
  If Selection.Cells.Count <> 65536 Then
    Call MsgBox("You must select an entire column", vbCritical)
    End
  End If
  
  ”’Set a range variable to all data in selected column
  Set objRange = Range(Selection.Cells(2, 1), Selection.Cells(65536, 1).End(xlUp))

  ”’ Add a column for formulas
  With objRange
    .EntireColumn.Insert
    .Offset(0, -1).FormulaR1C1 = "=IF(AND(NOT(ISNA(R[-1]C))," & _
                                 "R[-1]C[1]<>RC[1]),0,"""")"
    ”’ Convert to values
    .Offset(0, -1) = .Offset(0, -1).Value
    ”’ Set variable to 0
    Set objRange = .Offset(0, -1).SpecialCells(xlCellTypeConstants, xlNumbers)
  End With
   
  ”’ Add a row at each 0
  If WorksheetFunction.CountIf(objRange, 0) > 0 Then
    Call objRange.EntireRow.Insert
  End If
  
  ”’ Reset variable for next formulas
  Set objRange = Range(Selection.Cells(2, 1), Selection.Cells(65536, 1).End(xlUp))
  
  ”’ Add the formula to add 0
  objRange.FormulaR1C1 = "=IF(OR(RC[1]="""",R[-1]C[1]=""""),""""," & _
                         "IF(RC[1]<>R[-1]C[1],0))"
  ”’ Convert to values
  objRange = objRange.Value
  
  ”’ Set variable to 0 cells if any
  If WorksheetFunction.CountIf(objRange, 0) > 0 Then
    Set objRange = objRange.SpecialCells(xlCellTypeConstants, xlNumbers)
    ”’ Add a row at each 0
    objRange.EntireRow.Insert
  End If
  
  ”’ Delete added Column
  Call objRange.Columns(1).EntireColumn.Delete

  ”’ Remove an instance
  Set objRange = Nothing

  ”’ Exit the Sub
  Exit Sub
    
”’ Define the subfunction for errorhandling.
ErrHandler:
  ”’ Error.
  ”’Call gobjLogFile.Error("ThisWorkbook, function : InsertRowAtEachChange", _
                         "Description goes here", Err.Number, Err.Description)
  
  ”’ Resume anyway.

  Resume Next

End Sub 

 

About these ads
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: