Excel VBA - Copying Dynamic Range without Headers - Issue

Dear all,

I'm trying to copy dynamic range of data from one sheet to another without headers.

Selection to copy the range of data doesn't work when values are missing in the 2nd column(for ex.).

PFA.

Input 1 sheet contains data with no blanks. Range Selection works without any issues.

Input 2 sheet contains data with few blank. Range Selection doesn't work as expected and selects only few columns.

Debug with two inputs. Kindly modify the code depending on the sheet you have chosen as Input.

The issue is in the below Code:

Please Login or Register  to view this content.

Need your help to make this work for any kind of data.

P.S. I can ensure that the 1st column will not have any blanks in starting or in between.

Thanks,
Vinod Krishna

Hi Vinod,

I would recommend to make use of the Cells.Find function like this to be grabbing all of the data from your sheet, even when there is missing data:

Please Login or Register  to view this content.

Dear rkey,

Thank you for the code. Works like a Charm on any kind of input

Wish the code would have been a few liner

Regards,
Vinod Krishna

FWIW:

Please Login or Register  to view this content.

Dear John,

Thank you for the simplifying things

I'm trying to use your code as a Reusable Code.

Could you please help me only with " Selecting the Range; and Copying " logic?

P.S The no. of rows and columns will keep varying on a monthly basis.

Thanks,
Vinod Krishna

Vinod:

The code as written will copy all rows and columns in the activesheet except row1 (ie .UsedRange) irregardless of the number or rows or columns and paste too Sheets("Output"). And place the copied cells one row below the last used cell in Column A in Sheets("Output"). (ie Range("A" & rows.count).End(3)(2). BTW Range("A" & rows.count).End(3)(2) is a shorter version of Range("A" & rows.count).(xlUp).Offset(1,).