select range

[philmetz]

I need to select a range, I have this code

Please Login or Register  to view this content.

Basically I need to select a range of cells which could be from A2:someColumnLetter & SomeRow#

Dont think the above code quite works?

[JBeaucaire]

First, if possible, try not to use Usedrange to spot row and column maximums. In most cases it's fine, but there are situations where the results are not correct. If you can specify a starting point reliably, do so.

Here are my two most common approaches to getting LR and LC variables filled in:

Please Login or Register  to view this content.

To answer your question, the LC variable returns a numeric answer. So you'll need to switch to the Cells() function so that you can use a numeric value for the column portion.

Please Login or Register  to view this content.

Lastly, you didn't show what happens next...but when writing code by hand (or editing macro recorder code) there is seldom ever a need to "select" a range to do what you want. If you select, the next code invariably starts with Selection.Copy or whatever.

There's no need. Skip the selecting and give the command directly:

Please Login or Register  to view this content.

[philmetz]

Thanks that code worked with a small modification (added -4 to end of column)

See attachment
Now see if I Click on "Hide/Unhide $" and then click on "Copy", open Word and paste it in there, the table goes off the page.

But then if I click "Hide/Unhide K" and click "Copy" and then open word and paste that there it works fine keeping the formatting.

How can I fix that?

[royUK]

Are you saying that yo are trying to paste to Word/ If so why not just print the workbook?

[philmetz]

Well because ill paste into an already formated document, im just taking that data into a word file that I have read

[romperstomper]

FYI, using Usedrange and SpecialCells(xlCellTypeLastCell) will return the same 'last' cell and hence are both subject to the same potential error.

[philmetz]

But how come formatting is not kept when I paste into word?
Could be because I am hiding cells.

[JBeaucaire]

FYI, using Usedrange and SpecialCells(xlCellTypeLastCell) will return the same 'last' cell and hence are both subject to the same potential error.

I'm not sure this is correct. Based on the OPs original technique, if a sheet ONLY has values in cells C4:E5, the LastR and LastC values come up to be:

Please Login or Register  to view this content.

Since the last cell with data is E5, the answers should have been 5 and 5, so this technique does not work when cell A1 is empty.

This next technique always spots the last used cell in sheet and gives the proper values.

Please Login or Register  to view this content.

[romperstomper]

I think you missed my point, so perhaps I wasn't clear. UsedRange and xlCellTypeLastCell both suffer from the problem you mentioned whereby they can include cells that are no longer actually in use (or perhaps that was not what you were referring to?) In other words, the bottom right cell of UsedRange is what xlCellTypeLastCell refers to. Both are inaccurate on occasion.

[JBeaucaire]

No, the error I was point out is that UsedRange.Columns.Count will count the number of columns with data in it, not tell you what the last column with any data in it is. UsedRange.Rows.Count...same thing. My example above demonstrates the incorrect results.

I understand what you mean, but xlCellTypeLastCell is still a usable technique for spotting the lastrow and lastcolumn for the entire sheet. When you're trying to insure you get all the data on the sheet, at least this does always work.

For finesse range copying, I would use a more surgical selection technique, anyway, but this will always work for getting it ALL, whereas the OPs original technique could/would most definitely return values far too small.

If you think the xlCellTypeLast has a demonstrable flaw that results in data not getting including, please explain. I'd hate to have a land mine on this, as you've warned.

[philmetz]

umm so how come when I click copy and paste in MS Word, the description column width is made wider a bit? How can i stop this?

[JBeaucaire]

Excel and Word have a lot of their own "display" characteristics. Pasting loose data from one to the other will result in some shifting, at least it always has for me.

The only way I ever insure the Word version is digit-for-digit identical when pasted is to pastespecial as a Picture.

NOTE: The copy technique used in your sheet will work fine since there are no blank rows/columns in your data, but as explained above, this WILL bite you one day and worse, you might not know it has. I would adjust to a different LR/LC technique now so it can never happen to you.

[philmetz]

what you mean LR LC?

so theres no way of copying with the formatting? hmm ok

[JBeaucaire]

what you mean LR LC?

Have you been following the discussion above regarding the technique you are using for determine last row (LR) and last column (LC) in your sheet. As noted, your technique works for this sheet, but the technique has a major flaw in it of which you should be aware.

so theres no way of copying with the formatting? hmm ok

That's not what I said, it is copying with the formatting, but once it arrives in Word, if it's loose text, Word can adjust it. The formatting is all still there, but column widths can be affected by the way the programs are displaying data.

[philmetz]

yea thats what I ment, i cant keep the width at the same amount in excel>?

[romperstomper]

If you think the xlCellTypeLast has a demonstrable flaw that results in data not getting including, please explain. I'd hate to have a land mine on this, as you've warned.

No, quite the reverse - when it's wrong, you end up processing more cells than required, not fewer.
You can use Usedrange but you need to add .Row to .Rows.Count and subtract 1 and similarly with the columns. That at least doesn't require you to know the start cell, but again can return too large a range.

[JBeaucaire]

Well, we're discussing mildly related issues. As I'd indicated, I would be more surgical in my method of determining LR and LC for specific ranges, but when an entire sheet's worth of data is being sought, truly the last cell in the sheet, then xlCellTypeLastCell is fine. Can extra cells be included, yes, absolutely, but that's not nearly the problem that too little data being processed creates.