We help IT Professionals succeed at work.

Ctrl keys not going to the bottom of the region

Mike French
Mike French used Ask the Experts™
on
Can someone please explain to me why Excel is doing this? In the picture below, if I click in cell "B2" or "B3" and use the ctrl and down arrow key it will only go to cell "B4"? if I click in "B4", and do the same, it goes all the way to the bottom; "B18". The same thing occurs with using vba code for end(xldown). This is a price list i downloaded in Excel format.
Picture of excel page
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
You are probably noticing a "feature" of the Table user interface.

I copied data like your pipe fittings in a worksheet with merged cells for the title in row 2, outline border in rows 2 and 3, and another outline border for rows 4 through 18.

If the data was not in a Table, when I clicked anywhere in the merged cells in row 2, Control + Down arrow took me to cell B18.

If I put the data in a Table (using row 3 for header labels), when I clicked anywhere in the merged cells in row 2, Control + Down arrow took me to cell B4. Click Control + Down arrow again took me to cell B18.

I'm using Excel 2016 on an Office 365 subscription (Office Insider Fast channel), so I likely have a newer version than you do.
Roy CoxGroup Finance Manager

Commented:
When working with data in Excel you should only have one Header Row.

In an ordinary, unformatted table of data, place an empty row between the two rows and make it very narro.

In data formatted as a Table first convert to a Range, then select all the data excluding the top row and convert back to a Table.

It is best to avoid MergedCells and use Center across Selection from the Format Cells dialog -> Alignment Tab

Center across selection
Mike FrenchMississippi Regional Manager

Author

Commented:
Hey guys,

I appreciate the comments. It didn't occur to me that these were tables. As I said, this is a price list I downloaded from one of our vendors. I was trying to loop through it an organize the data in another format. It is a protected workbook so I can't change any of the tables. I can, however, copy and paste it as values only and get rid of the tables. there are 8 worksheets and several tables per sheet.

Thanks again!
Roy CoxGroup Finance Manager

Commented:
Are the sheets protected or the workbook?
Mike FrenchMississippi Regional Manager

Author

Commented:
the workbook
Roy CoxGroup Finance Manager

Commented:
Can you contact the providers for the password?
Mississippi Regional Manager
Commented:
I am certain they wouldn't give it to me. It's not a big problem. Just something I was trying to do. I appreciate all your help!
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
I found your table in a pdf on the Tyler Union website, and then opened it as an editable Word document (Word 2013 or later has that feature). I was surprised to find that the data in that Word document was imported as a single column Word Table format. After many editing steps, I was able to get the data into useful form in Excel.

If you are getting an Excel workbook from Tyler Union directly, that is a much simpler way of doing it. Even if you have to put up with password protection, it is easier to do that than all the horsing around I had to do.