VBA select all data in row and columns

[Pinaceous]

Hi All,

How do I provide vba to select all data from column(s) until the last row of data?

 With .ActiveSheet
    With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
      r = .Row
      c = .Column
    End With
    Set xlRng = .Range(.Cells(1, 1), .Cells(r, c))
  End With
End With

Because this is not working for me??

Thanks,
pinaceous

 

[Skyybot]

ActiveSheet.Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)).Select

 

[Peter_SSs]

UsedRange can often include ranges that you are not expecting. If still having difficulties, try this.

Dim lr As Long, lc As Long
Dim xlRng As Range

With ActiveSheet
  lr = .Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  lc = .Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  Set xlRng = .Range(.Cells(1, 1), .Cells(lr, lc))
End With

For the future, any comments like this would be better if accompanied by a description &/or examples of just what "not working" means. (eg error, crash, nothing happens, range is set to abc when I was expecting xyz etc)

this is not working

 

[Pinaceous]

Thanks Peter SSs!

 

[Peter_SSs]

You're welcome.