Select rows with data

[vandanavai]

Please help me to proceed with following.

I have worksheet with data . Everytime number of rows with some data in it changes . Like sometime there are 100 rows with data or other tome it may be 57 only. I want to write VB code to select only those rows which have data. Thereafter I will use Pivot Table code to get required information. I know VB code to generate Pivot Code. But what happens is that if any blank row gets selected in data range for Pivot table , thereafter grouping by "Month" is not done.

Please help. I don't know I could explain my problem properly or not.

[jtp]

I am guessing the rows of data don't have empty rows mixed in with them. Is this correct? If so then you can find the last row by using

Please Login or Register  to view this content.

then apply that to your Pivot Code. Probably easier would be to

Please Login or Register  to view this content.

This will grab all the data on the sheet.

HTH

[vandanavai]

Thanks JTP for help. Though I understood code logically, it is bit difficult to add it in my file practically. Can you go through attached file? In Module 1 ( which contains macro ) instead of R1C1:R8C4 , I want range with data only.

[jtp]

I can't get it to work the way I normally do this and don't have any references around me but I found a way to work around it. To be honest I don't like what I did but it works.

Please Login or Register  to view this content.

Couldn't use the .UsedRange function because of the "Keyboard Shortcut: Ctrl+a" you have on the Data sheet so i used what you see above.

[vandanavai]

Thanks JTP

Evenif you are not happy with method of doing this, I am So happy as it worked perfectly. This wahat I was looking for. It is going to help me a lot.

Thanks once again