SpecialCells xlCellTypeLastCell selecting additional blank columns

[MattGreer]

I have a table of data. The only odd thing about it, I suppose, is that column A is not completely filled with data. There's an entry ever 2 to 50 rows; it just depends and it's not something I can depend on.

I'm trying to create a structured table around this set of data. The ActiveCell is cell A1, and I do have headers. There's three columns of data and columns B and C are completely filled.

When I run the code:
Set rTable = Range(ActiveCell, ActiveCell.SpecialCells(xlCellTypeLastCell))
rTable.Select

The range includes the first three columns... and four more columns after that. UNLESS I run the code in debug mode and SAVE the spreadsheet just before I set the range.

I can't figure out why. Anyone know why this is happening? I guess I could add the 'save spreadsheet' code in the macro but I would really, really not like to do that.

Thanks!!


Matt

 

[MattGreer]

Aaaaaanddd... just before I posted this I figured out saving the spreadsheet fixes the "bug", and right after I post it leads me to search for this kind of issue and, I find a solution.

Basically, the used range of the current sheet needs to be reset before I use xlCellTypeLastCell.

Found this, and fixed it by adding a little subroutine:

Public Sub reset_usedrange()
Dim a As Long
a = ActiveSheet.UsedRange.Rows.Count
End Sub




Thanks!!


Matt

 

[MattGreer]

But I'll add this. For my purposes this is more accurate. For some reason the very last table ends up with more rows than it should have. All the other tables are fine, but not that one.

Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Set rTable = Selection


OK, all done with this one.

Thanks!!


Matt

 

[SkipVought]

Matt,

You appear to have INVISIBLE DATA on your sheet. I've experienced this on occasion.

SELECT the COLUMNS to the right of the table and DELETE, selecting Shift columns left.

SELECT the ROWS below the table and DELETE, selecting Shift rows up.

When you do this selecting of columns and rows, be sure you select the unwanted columns or rows, which would be columns D & E and any rows at the bottom of your table that do not contain data.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!