Automatic range selection problem

[Pepper75]

Dear Mr Excel, Dear users,

Being new to VBA is quite a challenge and I am finding that this one is quite a big one! I would like to request your help in helping me find a solution to an automatic range issue.

Problem : I have two different excel workbooks both having a different number of columns and a different number of rows
example -> workbook 1: 5 columns /10 rows
workbook 2: 12 columns / 200 rows

Target: What I would like to obtain is a code that would work on both workbooks and create a sum and a count of all rows but that would adapt to a change in the number of rows and columns if the code is copied from one workbook to another.

example -> John Peter Paul James William Count Sum
Monday 1 5 7 0 3 4 16
Tuesday 5 0 0 12 0 2 17

Many thanks in advance to all for your help and time!

François

 

[farmerscott]

1. send me a 'private message' and I will give you my email address.

2. which column has the longest list of data? If it is not C then change the col below in red.

lr = Worksheets("sheet1").Cells(Rows.Count, "[COLOR=#ff0000]C[/COLOR]").End(xlUp).Row

 

[Pepper75]

FarmerScott,

Now that you mention this option I went to check out my worksheet..my data really stops on row 621 in column c but goes further in other columns.
I just tried it out with another column and it works!
Since my column will always have as last row an empty cell (with data in the other columns), is there a way to give it a +1?
example -> data goes on till row 620 but my whole data goes on till row 621 (the empty cell in column B)

Thanks a lot!!

François

 

[farmerscott]

Francois,

now we are getting somewhere.....you can either-

1. do as you suggested and add 1, if your data always finishes 1 row after Col C finishes.

lr = Worksheets("sheet1").Cells(Rows.Count, "C").End(xlUp).Row+1

2. find which col of data is the longest and change the "C" below to the desired col.

lr = Worksheets("sheet1").Cells(Rows.Count, "[COLOR=#ff0000]C[/COLOR]").End(xlUp).Row

cheers

FarmerScott

 

[Pepper75]

FarmerScott,

After trying out your 1st method "lr = Worksheets("sheet1").Cells(Rows.Count, "C").End(xlUp).Row+1" everything works great!
I will just have to make sure that all my worksheets all have the same layout.

I wish to thank you for your time and knowledge!

Cheers,

François