Clever Geek Handbook

Excel: usedrange with table columns

Can I use UsedRange to select from the first to the last column of a table?

Here's the source code:

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

But it gets from the first row to the last Excel maximum and I don't want that. 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 it doesn't seem to work here, so I doubted there was a proper way to do it, or UseRange is not the best one for doing this.

Also, after that I want to use THIS code provided by Jeeped, but I need to know if this problem can be solved first.

+3


source to share


2 answers


You can 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 can use .UsedRange with Intersect

with Intersect(ws.UsedRange, ws.columns("E"))
    'do something with the cells in column E
end with
+3


source


Try this, but never use .Select

.



With ws 
    .Range("E2:E" & LastRow).Select
End with
0


source






More articles:


All Articles