Macro Needs To Select Last Column

[nikonlubber2003]

Hi,

I need to create a macro that will select the last column in a worksheet. The last column can be anywhere from column "D" to column "H" on any given day. Its always different. I only need the information in the last column and then not all the information in the column, just certain cells. So the macro cannot select the entire column. Is there any code I can use for the macro to find the last column in use and copy what I need when it may be different every day? Any help would be greatly appreciated.

Thanx

 

[Tom Urtis]

Just for starters there are several ways to identify the last column. Here are 2, and from there you can use the Cells or Columns index reference to do what you want.


MsgBox ActiveSheet.UsedRange.Columns.Count

MsgBox Range("IV1").End(xlToLeft).Column


Example:

Dim Col As Integer
Col = ActiveSheet.UsedRange.Columns.Count
Cells(1, Col).Activate

This will select the cell in row 1 of the last column being used.

 

[Tom Urtis]

Too late for an edit, here's another method I forgot to throw in, FWIW.

Dim LastCol As Integer
LastCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
MsgBox LastCol

 

[bernardes]

Your tips are very good.

André Luiz Bernardes