Hello,
My company generates reports that need to be formatted before being sent to
clients. They can be anywhere from 2 pages to over 200 (all on one worksheet)
depending on the data, but are typically under 30 pages. The formatting for
each page will be the same (columns are different from each other, but all
pages will look similar). Each page contains anywhere from 1 to 10 rows, and
8 to 25 or so columns.

I would like to set up a macro that, after clicking on the upper left most
cell of the area needing formatting, would then a) count the number of rows
with values in them; b) count the number of columns with values; c) select
this combination of rows and columns in sections; and d) apply certain
borders to each section.

I was able to set up a macro for one page within the worksheet, but not
every page has the same number of rows and columns so obviously it doesn't
work on every page. This is what I have for that one page (I took out the
actual format wording because it was so lengthy and not really important to
the bulk of the formula):

This particular macro is set up for 10 rows and 18 columns.

Sub Macro19()

ActiveCell.Offset(2, 0).Select
ActiveCell.Resize(8, 1).Select
'here is where I apply my formatting
ActiveCell.Offset(0, 1).Select
ActiveCell.Resize(8, 2).Select
'application of formatting
ActiveCell.Offset(-2, 5).Select
ActiveCell.Resize(2, 10).Select
'application of formatting
ActiveCell.Offset(2, 0).Select
ActiveCell.Resize(8, 10).Select
'more formatting

End Sub


Thanks in advance!

Are there typically empty rows in the data? If not, then use the following
code to find the size of your data:

LastRow = cells(application.rows.count, 1).end(xlup).row
LastCol = cells(1, application.columns.count).end(xltoleft).column

Then, select the columns individually like so:

range(cells(2, 1), cells(LastRow, 1)).select

The line above selects the data in the first column (assuming a header row),
and you can select the other columns similarly. If this helps, let me know.

HTH,
Matthew Pfluger

"SLW612" wrote:

Hello,
My company generates reports that need to be formatted before being sent to
clients. They can be anywhere from 2 pages to over 200 (all on one worksheet)
depending on the data, but are typically under 30 pages. The formatting for
each page will be the same (columns are different from each other, but all
pages will look similar). Each page contains anywhere from 1 to 10 rows, and
8 to 25 or so columns.

I would like to set up a macro that, after clicking on the upper left most
cell of the area needing formatting, would then a) count the number of rows
with values in them; b) count the number of columns with values; c) select
this combination of rows and columns in sections; and d) apply certain
borders to each section.

I was able to set up a macro for one page within the worksheet, but not
every page has the same number of rows and columns so obviously it doesn't
work on every page. This is what I have for that one page (I took out the
actual format wording because it was so lengthy and not really important to
the bulk of the formula):

This particular macro is set up for 10 rows and 18 columns.

Sub Macro19()

ActiveCell.Offset(2, 0).Select
ActiveCell.Resize(8, 1).Select
'here is where I apply my formatting
ActiveCell.Offset(0, 1).Select
ActiveCell.Resize(8, 2).Select
'application of formatting
ActiveCell.Offset(-2, 5).Select
ActiveCell.Resize(2, 10).Select
'application of formatting
ActiveCell.Offset(2, 0).Select
ActiveCell.Resize(8, 10).Select
'more formatting

End Sub


Thanks in advance!

I'm sorry ... I don't think I was as accurate as I needed to be. I will try
to clarify.

Each page of the spreadsheet (when I say page I am referring to print areas)
actually has probably 30 rows (with information such as client name and
number, address, current date, etc.), but of those 30 I only need to format a
section in the middle that is about 10 rows and a maximum of 25 columns.
That's why I wanted to be able to click a controlled starting point (upper
left most cell in the section I want to format) and run the macro to count
non-blank cells from that point down for # of rows and across for # of
columns, then use these values as range points for my formatting. The number
of active rows and columns is potentially different for each page on the
spreadsheet.

So technically, I guess there are blanks in the spreadsheet. But the way I
want to do it is to look at each 10row x 25col section individually, and
format accordingly.

"Matthew Pfluger" wrote:

Are there typically empty rows in the data? If not, then use the following
code to find the size of your data:

LastRow = cells(application.rows.count, 1).end(xlup).row
LastCol = cells(1, application.columns.count).end(xltoleft).column

Then, select the columns individually like so:

range(cells(2, 1), cells(LastRow, 1)).select

The line above selects the data in the first column (assuming a header row),
and you can select the other columns similarly. If this helps, let me know.

HTH,
Matthew Pfluger

"SLW612" wrote:

Hello,
My company generates reports that need to be formatted before being sent to
clients. They can be anywhere from 2 pages to over 200 (all on one worksheet)
depending on the data, but are typically under 30 pages. The formatting for
each page will be the same (columns are different from each other, but all
pages will look similar). Each page contains anywhere from 1 to 10 rows, and
8 to 25 or so columns.

I would like to set up a macro that, after clicking on the upper left most
cell of the area needing formatting, would then a) count the number of rows
with values in them; b) count the number of columns with values; c) select
this combination of rows and columns in sections; and d) apply certain
borders to each section.

I was able to set up a macro for one page within the worksheet, but not
every page has the same number of rows and columns so obviously it doesn't
work on every page. This is what I have for that one page (I took out the
actual format wording because it was so lengthy and not really important to
the bulk of the formula):

This particular macro is set up for 10 rows and 18 columns.

Sub Macro19()

ActiveCell.Offset(2, 0).Select
ActiveCell.Resize(8, 1).Select
'here is where I apply my formatting
ActiveCell.Offset(0, 1).Select
ActiveCell.Resize(8, 2).Select
'application of formatting
ActiveCell.Offset(-2, 5).Select
ActiveCell.Resize(2, 10).Select
'application of formatting
ActiveCell.Offset(2, 0).Select
ActiveCell.Resize(8, 10).Select
'more formatting

End Sub


Thanks in advance!

Not a problem. Since you plan on telling the macro where you want to start
(by clicking on the start cell), how about this:

Sub work()

Dim CurRow As Long
Dim CurCol As Long
Dim LastRow As Long
Dim LastCol As Long

' Get last row of page
CurRow = Selection.Row
LastRow = Selection.End(xlDown).Row

' Get last column of page
CurCol = Selection.Column
LastCol = Selection.End(xlToRight).Column

' Select each column of page
For i = CurCol To LastCol
Range(Cells(CurRow, i), Cells(LastRow, i)).Select

' Apply formatting here
Next

End Sub

The code finds the last cell to the right and down and selects each column
within that range individually. You can then format how you wish.

Does this help?

Matthew Pfluger
"SLW612" wrote:

I'm sorry ... I don't think I was as accurate as I needed to be. I will try
to clarify.

Each page of the spreadsheet (when I say page I am referring to print areas)
actually has probably 30 rows (with information such as client name and
number, address, current date, etc.), but of those 30 I only need to format a
section in the middle that is about 10 rows and a maximum of 25 columns.
That's why I wanted to be able to click a controlled starting point (upper
left most cell in the section I want to format) and run the macro to count
non-blank cells from that point down for # of rows and across for # of
columns, then use these values as range points for my formatting. The number
of active rows and columns is potentially different for each page on the
spreadsheet.

So technically, I guess there are blanks in the spreadsheet. But the way I
want to do it is to look at each 10row x 25col section individually, and
format accordingly.

"Matthew Pfluger" wrote:

Are there typically empty rows in the data? If not, then use the following
code to find the size of your data:

LastRow = cells(application.rows.count, 1).end(xlup).row
LastCol = cells(1, application.columns.count).end(xltoleft).column

Then, select the columns individually like so:

range(cells(2, 1), cells(LastRow, 1)).select

The line above selects the data in the first column (assuming a header row),
and you can select the other columns similarly. If this helps, let me know.

HTH,
Matthew Pfluger

"SLW612" wrote:

Hello,
My company generates reports that need to be formatted before being sent to
clients. They can be anywhere from 2 pages to over 200 (all on one worksheet)
depending on the data, but are typically under 30 pages. The formatting for
each page will be the same (columns are different from each other, but all
pages will look similar). Each page contains anywhere from 1 to 10 rows, and
8 to 25 or so columns.

I would like to set up a macro that, after clicking on the upper left most
cell of the area needing formatting, would then a) count the number of rows
with values in them; b) count the number of columns with values; c) select
this combination of rows and columns in sections; and d) apply certain
borders to each section.

I was able to set up a macro for one page within the worksheet, but not
every page has the same number of rows and columns so obviously it doesn't
work on every page. This is what I have for that one page (I took out the
actual format wording because it was so lengthy and not really important to
the bulk of the formula):

This particular macro is set up for 10 rows and 18 columns.

Sub Macro19()

ActiveCell.Offset(2, 0).Select
ActiveCell.Resize(8, 1).Select
'here is where I apply my formatting
ActiveCell.Offset(0, 1).Select
ActiveCell.Resize(8, 2).Select
'application of formatting
ActiveCell.Offset(-2, 5).Select
ActiveCell.Resize(2, 10).Select
'application of formatting
ActiveCell.Offset(2, 0).Select
ActiveCell.Resize(8, 10).Select
'more formatting

End Sub


Thanks in advance!

Hmm ... that isn't exactly what I am looking for. I will try to describe my
spreadsheet differently:

I want to do this:
Click on any cell in a worksheet (for the example, we will use A40). From
that cell, the formula needs to apply to the area that is 10 rows high
(including starting row), as well as 25 columns across. (This example: area
is A40:Y49)

It should count the number of cells in the 6th column over (of the 10-row
high area) that have any entry (i.e. not blank). We'll call this "numRows."
Example: rows F40:F49 have 6 values so the value of "numRows" should be 6.

It then will need to count the number of cells in the starting row that are
also not blank and call it "numCol." Example: columns A40:Y40 have 4 values
so the value of "numCol" should be 4.

Now I need to apply certain formatting to the area that is offset 2 rows
down from the starting cell, and as tall as "numRows" and as wide as
"numCol". Example: macro will select cell A42 and resize the selection with
the number of rows as "numRows", and number of columns as "numCol."

My data specifically will never have any values in the area that is 2 cells
down by 8 cells across from the starting cell, or area A40:H42, so I do not
want to select this area.

Therefore, the area I need to select is A42:L47. From here I can figure out
how to apply formatting, but it's the selecting and how to do the counting
that I can't figure out.

I really appreciate all your help!



"Matthew Pfluger" wrote:

Not a problem. Since you plan on telling the macro where you want to start
(by clicking on the start cell), how about this:

Sub work()

Dim CurRow As Long
Dim CurCol As Long
Dim LastRow As Long
Dim LastCol As Long

' Get last row of page
CurRow = Selection.Row
LastRow = Selection.End(xlDown).Row

' Get last column of page
CurCol = Selection.Column
LastCol = Selection.End(xlToRight).Column

' Select each column of page
For i = CurCol To LastCol
Range(Cells(CurRow, i), Cells(LastRow, i)).Select

' Apply formatting here
Next

End Sub

The code finds the last cell to the right and down and selects each column
within that range individually. You can then format how you wish.

Does this help?

Matthew Pfluger
"SLW612" wrote:

I'm sorry ... I don't think I was as accurate as I needed to be. I will try
to clarify.

Each page of the spreadsheet (when I say page I am referring to print areas)
actually has probably 30 rows (with information such as client name and
number, address, current date, etc.), but of those 30 I only need to format a
section in the middle that is about 10 rows and a maximum of 25 columns.
That's why I wanted to be able to click a controlled starting point (upper
left most cell in the section I want to format) and run the macro to count
non-blank cells from that point down for # of rows and across for # of
columns, then use these values as range points for my formatting. The number
of active rows and columns is potentially different for each page on the
spreadsheet.

So technically, I guess there are blanks in the spreadsheet. But the way I
want to do it is to look at each 10row x 25col section individually, and
format accordingly.

"Matthew Pfluger" wrote:

Are there typically empty rows in the data? If not, then use the following
code to find the size of your data:

LastRow = cells(application.rows.count, 1).end(xlup).row
LastCol = cells(1, application.columns.count).end(xltoleft).column

Then, select the columns individually like so:

range(cells(2, 1), cells(LastRow, 1)).select

The line above selects the data in the first column (assuming a header row),
and you can select the other columns similarly. If this helps, let me know.

HTH,
Matthew Pfluger

"SLW612" wrote:

Hello,
My company generates reports that need to be formatted before being sent to
clients. They can be anywhere from 2 pages to over 200 (all on one worksheet)
depending on the data, but are typically under 30 pages. The formatting for
each page will be the same (columns are different from each other, but all
pages will look similar). Each page contains anywhere from 1 to 10 rows, and
8 to 25 or so columns.

I would like to set up a macro that, after clicking on the upper left most
cell of the area needing formatting, would then a) count the number of rows
with values in them; b) count the number of columns with values; c) select
this combination of rows and columns in sections; and d) apply certain
borders to each section.

I was able to set up a macro for one page within the worksheet, but not
every page has the same number of rows and columns so obviously it doesn't
work on every page. This is what I have for that one page (I took out the
actual format wording because it was so lengthy and not really important to
the bulk of the formula):

This particular macro is set up for 10 rows and 18 columns.

Sub Macro19()

ActiveCell.Offset(2, 0).Select
ActiveCell.Resize(8, 1).Select
'here is where I apply my formatting
ActiveCell.Offset(0, 1).Select
ActiveCell.Resize(8, 2).Select
'application of formatting
ActiveCell.Offset(-2, 5).Select
ActiveCell.Resize(2, 10).Select
'application of formatting
ActiveCell.Offset(2, 0).Select
ActiveCell.Resize(8, 10).Select
'more formatting

End Sub


Thanks in advance!

Terrific! I can work with this. I was having trouble getting the count of
non-blank cells and then turning that into a number I could use, but I think
this will be perfect. Thanks for your help!

"Matthew Pfluger" wrote:

Perhaps this is more what you mean.

Sub GetRangeToFormat()

' Get range to count
Dim rCountRange As Range
Set rCountRange = Selection.Offset(2, 0).Resize(8, 25)
rCountRange.Select

' Get range to count rows
Dim rColToCount As Range
Set rColToCount = rCountRange.Columns(6)
rColToCount.Select

' Count rows
Dim NumRows As Integer
NumRows = Application.WorksheetFunction.CountA(rColToCount)

' Get range to count columns
Dim rRowtoCount As Range
Set rRowtoCount = rCountRange.Rows(1).Offset(-2, 0)
rRowtoCount.Select

' Count columns
Dim NumCol As Integer
NumCol = Application.WorksheetFunction.CountA(rRowtoCount)

' Get range to format
Dim rRangeToFormat As Range
Set rRangeToFormat = Range(rCountRange.Cells(1, 1),
rCountRange.Cells(NumRows, NumCol + 8))
rRangeToFormat.Select

' Select first column
rRangeToFormat.Columns(1).Select

' Apply formatting

End Sub

You may have to modify the selections a bit to tweak it to what you want,
but I think that should work well. I tested it on a workbook similar to what
you describe in your post.

Tip: To make the final version run quicker, remove all lines containing
xRange.Select.

HTH,
Pflugs

Glad I could help. Goodness knows I've received tons of help from this forum
to get to where I am now.

Thanks for rating the post, too.

Matthew Pfluger

"SLW612" wrote:

Terrific! I can work with this. I was having trouble getting the count of
non-blank cells and then turning that into a number I could use, but I think
this will be perfect. Thanks for your help!

"Matthew Pfluger" wrote:

Perhaps this is more what you mean.

Sub GetRangeToFormat()

' Get range to count
Dim rCountRange As Range
Set rCountRange = Selection.Offset(2, 0).Resize(8, 25)
rCountRange.Select

' Get range to count rows
Dim rColToCount As Range
Set rColToCount = rCountRange.Columns(6)
rColToCount.Select

' Count rows
Dim NumRows As Integer
NumRows = Application.WorksheetFunction.CountA(rColToCount)

' Get range to count columns
Dim rRowtoCount As Range
Set rRowtoCount = rCountRange.Rows(1).Offset(-2, 0)
rRowtoCount.Select

' Count columns
Dim NumCol As Integer
NumCol = Application.WorksheetFunction.CountA(rRowtoCount)

' Get range to format
Dim rRangeToFormat As Range
Set rRangeToFormat = Range(rCountRange.Cells(1, 1),
rCountRange.Cells(NumRows, NumCol + 8))
rRangeToFormat.Select

' Select first column
rRangeToFormat.Columns(1).Select

' Apply formatting

End Sub

You may have to modify the selections a bit to tweak it to what you want,
but I think that should work well. I tested it on a workbook similar to what
you describe in your post.

Tip: To make the final version run quicker, remove all lines containing
xRange.Select.

HTH,
Pflugs