Removing Haning Blank Rows

Lets say you get a report with 30 rows of data, you delete the last two and then go to cell A1. If you hit Ctrl+Shft+End the cells are all highlight including the blank cells that were deleted. I know that you can delete the blank rows, save/exit the workbook and then reopen it to correct this problem. What I'm wondering is there an easy snippet of code that can be used to select the data but ignore the deleted areas.

In other words, and perhaps more clearly stated, If I have data in cells A1:B30 and use Ctrl+Shft+End cells A1:B30 are selected. If I then delete the data in A29:B30, return my cursor to A1, and hit Ctrl+Shft+End, the data selection is still A1:B30. Is there an easy way with code to make it select A1:B28? This is just a sample, the original report could have any number of rows and or columns and some times I get a report that already has blank rows at the end. I want to be able to select data only, no blank rows that may be lingering.

If it helps, I often use the Ctrl+Shft+End in conjunction with something like this to format my data:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1").CurrentRegion, , xlYes).Name = "Table1"

I'm attaching a simple test file that had data in A1:B30, and I've already deleted A29:B30. With the A1 selected you should see that Ctrl+Shft+End highlight all the way down to B30. Even Ctrl+End will take you to B30.

As always, thanks in advance for any assistance.

Whh3

Hi Whh3,

I find the following code to be reliable when trying to find the last row, after rows have been added or deleted.

Please Login or Register  to view this content.

Lewis

Hi Lewis!

Thanks for the quick response. Your code does indeed find the and report the Last Row, but what I'm looking for is it to then select those rows, so I can apply some general formatting - like making it into a table. Sorry if that wasn't clear in my initial post.

Whh3

Hi,

It's difficult for me to directly answer your question, since I don't use tables. The following tested and working example code may help you do what you want. It is easier for me to create (and debug) a range when I:
a. Use a lot of intermediate variables
b. Use the Range(sRange) syntax rather than the Range(Cells(1, 1), Cells(5, 5)) syntax. This is especially true when dealing with more than one sheet, since the Cells syntax usually requires a Sheet qualifier.

Please Login or Register  to view this content.

I also find using Union and Intersect quite useful when dealing with ranges.

If you have additional questions, it would probably be easier to answer if you uploaded a simple sample workbook with sample data in the area you want to manipulate. Describe what you add (or delete) and how you want to create (or modify) the range.

I hope this helps.

Lewis

Hi Lewis,

I think I'm making it more complicated than it needs to be. I did try the latest code, but ran into an error. Haven't had a chance to dig deeper on that yet. I'm attaching a pdf with screenshots to show you want I'm after and the error is in there too.

First choice would be for excel to delete the two empty rows and forget they were ever there.

Second option, which may be the easiest, is just highlight the area with data. I've worked around this for a while by deleting the extra rows, saving the workbook, closing it, and then reopening. Just thought I'd try to tackle it a better way.

Check out the attached file and see if that makes what I'm after clearer. Thanks for all your time on this!

The following code combines the CurrentRegion with finding the Last Row.

CurrentRegion only works for contiguous areas as you have noticed. One possible workaround is to be a single apostrophe in each allegedly empty cell. The cell will appear empty to the eye, but Excel will think there is something in the cell.

In the following code, do not put a duplicate copy of function LjmExcelColumnNumberToChar() in the same code module, or Excel will LOCK UP.

Please Login or Register  to view this content.

Thanks Lewis. This seems to work in my test file so will work with it my ongoing projects. Marking as Solved!