vb help please for autofill dynamic range down by adjacent column

benneesham

New Member
Joined
Mar 24, 2009
Messages
3
Hello,

I would like help writing a bit of VB code please?

For my work we are sent lists of addresses in Excel spreadsheets and we need to count how many postcodes are in each area when they arrive.
As the task was repetitive I used a recorded macro to perform the tasks each time. The problem is that the data can be in any order, and any length. The postcodes are not always in the same column.

We used to move them to the column letter stipulated in the recorded macro but are not allowed to do this now.
I have tried wrting a bit of code that does everything dynamically (i.e nothing selected or implicitly stated in the code) but am stuck on autofilling a column with COUNTIF function down as many cells as populated in the preceeding column (a unique list of data).
I would like help getting the autofill working if anyone can help please?

The range is dynamic along with the column position.

Here is the code I have tried so far:


Set rEnd = rng.Offset(0, 7).End(xlUp)
Set rStart = rng.Offset(0, 8).Cells(2, 1)
rng.Offset(0, 8).Cells(2, 1).AutoFill Destination:=Range(rStart & rEnd.Offset(0, 1)), Type:=xlFillDefault

Regards

Benn
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hello All,

I have solved this on my own now:

Range(rng.Offset(0, 7).Cells(2, 1), rng.Offset(0, 7).End(xlDown)).Offset(0, 1).FillDownFills the adjacent columsn with the formula down to desired length in a variable column location.

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top