Excel

Find First or Last Populated Column in a sheet

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

MWE

Description:

xlFirstCol finds the first column in a sheet with ?something? in any cell. xlLastCol finds the last column in a sheet with ?something? in any cell. 

Discussion:

Many procedures or macros in Excel need to know where useful data starts and ends on the target worksheet. Hardcoding first and last columns works, but makes the macro less useful. Integrating the search process for first useful and last useful columns is relatively easy to do; but why repeat the coding for each macro when the method is probably find ?non-blank? columns each time and a simple function, once written, can be used again and again. The function xlFirstCol returns the target sheet column number for the first column that is ?not blank?. The function xlLastCol returns the target sheet column number for the last column that is ?not blank?. In both cases, if any cell in a column is ?non-blank?, then the whole column is considered ?non-blank?. Both procedures have a single, optional argument, the target worksheet. If the target worksheet name is supplied, the column info returned is for the target worksheet. If the worksheet name is not supplied, both procedures default to the active worksheet. The example demonstrates both cases. 

Code:

instructions for use 

			
Option Explicit 
 
Sub Test_xlFirstLastCols() 
     
     '       Target Application:  MS Excel
     '       Demonstration:  display first and last non-blank columns in the active sheet
     '                       and one target sheet
     
    Dim SheetName As String 
     '
     '           display sheet name and results from xlFindFirstCol and xlFindLastCol
     '           for the active sheet.  Since activesheet is assumed if procs are called
     '           without a passed arguement, use that method here
     '
    MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _ 
    "First non-blank col = " & xlFirstCol() & vbCrLf & _ 
    "Last non-blank col = " & xlLastCol(), vbInformation, _ 
    "Active Sheet Demonstration" 
     '
     '           display sheet name and results from xlFindFirstCol and xlFindLastCol
     '           for "Sheet4".  Since this is not the active sheet, the sheet must
     '           be defined via the passed arguement.
     '
    SheetName = "Sheet4" 
    MsgBox "Worksheet name = " & SheetName & vbCrLf & _ 
    "First non-blank col = " & xlFirstCol(SheetName) & vbCrLf & _ 
    "Last non-blank col = " & xlLastCol(SheetName), vbInformation, _ 
    "Passed Sheet Name Demonstration" 
     
     
End Sub 
 
Function xlFirstCol(Optional WorksheetName As String) As Long 
     
     '       finds the first populated col in a worksheet
     
    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name 
    With Worksheets(WorksheetName) 
        On Error Resume Next 
        xlFirstCol = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _ 
        xlWhole, xlByColumns, xlNext).Column 
        If Err <> 0 Then xlFirstCol = 0 
    End With 
     
End Function 
 
Function xlLastCol(Optional WorksheetName As String) As Long 
     
     '       finds the last populated col in a worksheet
     
    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name 
    With Worksheets(WorksheetName) 
        On Error Resume Next 
        xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _ 
        xlWhole, xlByColumns, xlPrevious).Column 
        If Err <> 0 Then xlLastCol = 0 
    End With 
     
End Function

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, select the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file if desired.
 

Test the code:

  1. In the attached example, there are 5 sheets with varying amounts of data.
  2. Select any of these sheets (or create another sheet)
  3. Go to Tools | Macro | Macros (or Alt+F8) and double-click on Test_xlFirstLastCols
  4. The results for the active sheet and Sheet4 will be displayed.
  5. Each sheet has a text box with info on what you should expect from the demo. You may also click on any of these text boxes to execute the demo.
  6. (N.B. if you select Sheet4 as the active sheet the same result will be displayed twice.
  7. xlFirstCol and xlLastCol are functions to be called by a higher level procedure, further testing will depend on how the functions are used).
 

Sample File:

FirstLastCols.zip 17.27KB 

Approved by mdmackillop


This entry has been viewed 252 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express