3

Is there any way to use UsedRange to select from first to last column from a table?

Here is the original code:

Worksheets("Sheet1").UsedRange.Columns("E").Cells

But it gets from first row to last maximum of Excel and I don't want this. So I tried to do something like this:

Dim LastRow As Integer
    Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
  LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row  

With ws.UsedRange.Columns("E2:E & LastRow").Cells

Although this doesn't seem to work here, so I was in doubt if there is a correct way to do this or the UsedRange it's not the best for that.

In addition, after this I want to use THIS code provided by Jeeped, but I need to know if it's possible to solve this problem first.

paulinhax
  • 602
  • 3
  • 13
  • 23

2 Answers2

4

You could use the range directly,

with ws
    with .range(.cells(1, "E"), .cells(.rows.count, "E").end(xlup))
        'do something with the cells in column E
    end with
end with

You could use .UsedRange with Intersect

with Intersect(ws.UsedRange, ws.columns("E"))
    'do something with the cells in column E
end with
  • You should `Dim LastRow As Long`, not as an integer if you are going to use it. –  May 29 '17 at 12:59
  • thanks! it works now. I was really curious about UsedRange, I didn't know if I could type it like I do with Select. – paulinhax May 29 '17 at 13:09
  • Just remember that it is a property of a worksheet so (in the case above) it needs `ws` as a prefix to assign parent worksheet. –  May 29 '17 at 13:11
  • It seems that the first code works better than using Intersect, because it keeps draggin my table to the maximum Excel can reach – paulinhax May 29 '17 at 13:26
0

Try this, but never use .Select. 

With ws 
    .Range("E2:E" & LastRow).Select
End with
Plagon
  • 2,689
  • 1
  • 11
  • 23