Microsoft Excel - Find and select last column - Asked By Dan on 02-Aug-11 10:16 AM

Nevermind, I figured ot out. Thanks


I have figured out how to find the second to last column, but I can't seem to be able to select it.  I am trying to select the data out of the second to last column.

Sub LastColumn()
  
  Sheets("Sheet1").Select
   
    Dim LastCol As Long
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    LastCol = LastCol - 1
  Columns(LastCol).Select

    
    ActiveCell.Select
   
    ActiveCell.EntireColumn.Select
   
End Sub

Reena Jain replied to Dan on 02-Aug-11 10:32 AM
Hi,

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 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 Row, Column or Cell

You can use Edit>Go to-Special-Last cell to try and find the last cell in the active sheet, but it is not very reliable. The reasons are two-fold:

1. The last cell is only re-set when you save. This means if you enter any number or text in say, cell A10 and A20 of a new Worksheet, then delete the content of A20, the Edit>Go to-Special-Last cell will keep taking you to A20, until you save.

2. It picks up cell fomatting. Let's say you enter any text or number in cell A10 and then enter a valid date in cell A20 of a new Worksheet. Now delete the date in cell A20 and save. The Edit>Go to-Special-Last cell will still take you to A20. This is because entering a date in A20 has caused Excel to automatically format the cell from "General" to a Date format. To stop from going to A20 you will have to use Edit>Clear>All and then save.

http://www.ozgrid.com/VBA/ExcelRanges.htm
Pichart Y. replied to Dan on 02-Aug-11 10:34 AM
Hi Dan,

Should it be this one...
---------------------------------------------------
Sub LastColumn()
  
  Sheets("Sheet1").Select
    
    Dim LastCol As Long
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    LastCol = LastCol - 1
    Cells(1, LastCol).EntireColumn.Select
    
End Sub
---------------------------------------------------------

Pichart Y.
Pichart Y. replied to Dan on 02-Aug-11 10:34 AM
Hi Dan,

Should it be this one...
---------------------------------------------------
Sub LastColumn()
  
  Sheets("Sheet1").Select
    
    Dim LastCol As Long
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    LastCol = LastCol - 1
    Cells(1, LastCol).EntireColumn.Select
    
End Sub
---------------------------------------------------------

Pichart Y.
Ravi S replied to Dan on 02-Aug-11 10:34 AM
HI

try this

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198),99))

(or) 

try this

Try...

=LOOKUP(BigNum,A:A)-LOOKUP(BigNum,OFFSET(INDEX(A:A,1),0,0,MATCH(BigNum,A:A)-1))
__________________

refer the links also