Find Last Column With Data

Re: Find Last Column With Data

Hi

you can do this in VBA

Dim SelectColumn as Integer
SelectColumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column -3

I am not sure it can be done with formulae

Robert

Re: Find Last Column With Data

Hey there!

Quote from finnstones

i want a formula that takes a value from the 3rd to last" column to the left of the last column

but the last column is not static. so 1) i need to find the last column. and then 2) i need to take a value from the column 3 columns to the left of the last column. how do i do that using excel formulas?

thanks!

Ok, I think I managed to decypher that question... The following code should work, as long as you ment copy the information from the 3rd row from last of any given row.

Sub Macro1()


    Dim lRow As Long, lCol As Long
    '
    ' Store row and lastcolumn numbers.
    '
    lRow = ActiveCell.Row
    Range("IV" & lRow).End(xlToLeft).Select
    lCol = ActiveCell.Column
    
    '
    ' Select 1st column of current row.
    '
    Range("A" & lRow).Select
    
    '
    ' Verifies that last column is at least column D.
    '
    If lCol >= 4 Then
        Cells(lRow, lCol).Offset(0, -3).Copy Range("C1")    'If 4 or more, copy 3rd to last column info to cell C1.
    Else
        Range("C1") = "Not enough columns to perform"       'if not, put message in cell C1.
    End If
    
End Sub

Hopefully you had VBA in mind...

Ciao!

Re: Find Last Column With Data

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 Cells(1,LastColumn).EntireColumn.Address
	End If 
End Sub