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
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