Could someone please help with a macro or any other suggestion to format an
Excel sheet. Basically data is downloaded from a bank every week and the
size of the file can vary but the formatting is constant. That is each
record is 7 lines. So first record is line1-7, second record is line 8-14,
third record is line 15-21 and so on.

I need to format this data and basically I can record a macro for one data
set that is for the record on line 1 to 7. My question is how can I set it
to replicate so that it replicates for line 8-14 and then 15-21 and so on.
The source formatting remains the same except that the total length of the
file( no of records) changes every week.


Thanks very much for your help.

Regards

Sam

The macro you recorded probably starts off with something like this:
Range("A1:A7").Select
and then continues on with all of the formatting you applied to those cells.
Could be, if you selected multiple columns also that it looks like:
Range("A1:B7").Select

This is the guy you have to change, and to change it you need to add some
things to it:

Dim anyRange As String
Dim StartRow As Long
Dim EndRow As Long
Dim MaxRow As Long

MaxRow = Range("A" & Rows.Count).End(xlUp).Row
StartRow = 1
EndRow = 7
Do Until StartRow MaxRow
anyRange = "A" & StartRow & ":A" & EndRow
StartRow = StartRow + 7
EndRow = EndRow + 7
Range(anyRange).Select

put all of that except the last line in front of the first Range().Select
statement in the macro you recorded and replace the existing Range().Select
statement with that last line above [Range(anyRange).Select)

Then down at the bottom of everything just before the End Sub Statement, put
the word
Loop
on a line by itself.
That should do it for you. You may need to change a couple of lines of what
has been added. If the information you get comes into the Excel sheet in a
column other than "A", then change this line:
MaxRow = Range("A" & Rows.Count).End(xlUp).Row
to use the column identifier that the data came into. If it comes into more
than one column, pick the one that goes the farthest down the worksheet.

Also, again, change the column identifier(s) in this line of code:
anyRange = "A" & StartRow & ":A" & EndRow
to match your reality.

To explain what it does, line by line:
The four Dim statements simply declare the variables we will be using.

MaxRow = Range("A" & Rows.Count).End(xlUp).Row
That finds the last cell in the "A" column that has something in it - that
gives us a marker to know when we can stop looping through the formatting
process.

StartRow = 1
EndRow = 7
those two just initialize the start and end row numbers we need.

Do Until StartRow MaxRow
this is the beginning of the loop and it says to do everthing following it
all the way down to the 'Loop' statement you added until the value of
StartRow is greater than the value of our marker , MaxRow. So when StartRow
is beyond that marker row, things will stop getting done.

anyRange = "A" & StartRow & ":A" & EndRow
we are creating an address of the cells to be selected for the next
formatting actions. With the initialized values of StartRow and EndRow, the
first time through the loop, anyRange would appear as A1:A7 and that's what
you need for the .Select statement later.

StartRow = StartRow + 7
EndRow = EndRow + 7
these just bump the values of our 'pointers', getting them ready for the
next time through the loop. So StartRow will go 1, 8, 15, 23, etc, while
EndRow will become 7, 14, 22, etc. letting us grab 7 rows at a time to
format during the looping.

Range(anyRange).Select
this is where we choose the next group of 7 rows to format. And after that,
the code you recorded should work on the selection just fine.

If you originally chose entire rows to format, I suggest that you go back
and record another macro just selecting the columns (and 7 rows) that
actually contain the data from the bank - that'll allow the code I provided
to work without much changing. But if you actually need to format entire
rows, just say so and I'll tell you to change the line of code:
anyRange = "A" & StartRow & ":A" & EndRow
to become
anyRange = StartRow & ":" & EndRow

and change
Range(anyRange).Select
to become
Rows(anyRange).Select

and it should work just as well.

Hope this helps, and if I've confused you or you aren't clear on any of
this, just ask and myself or someone else will do our darnedest to help you
further.


"Sam Commar" wrote:

Could someone please help with a macro or any other suggestion to format an
Excel sheet. Basically data is downloaded from a bank every week and the
size of the file can vary but the formatting is constant. That is each
record is 7 lines. So first record is line1-7, second record is line 8-14,
third record is line 15-21 and so on.

I need to format this data and basically I can record a macro for one data
set that is for the record on line 1 to 7. My question is how can I set it
to replicate so that it replicates for line 8-14 and then 15-21 and so on.
The source formatting remains the same except that the total length of the
file( no of records) changes every week.


Thanks very much for your help.

Regards

Sam