Excel VBA variable print range 1

[swtrader]

I have a range of data from A1 thru H731. Rows (except for R1) in Column A are labels. Columns in Row 1 are day-by-day dates.

Say A2 is 7/1/2012, A3 is 7/2/2012, etc. -- out 2 years.

At various times a user may want to print only 9/1/2012 thru 11/30/2012 or 8/1/2013 thru 8/31/2013....including column A labels.

Once the VBA gets the Begin and End Date variables from cells populated by the user, what is the easiest way to set the print range to include (only) those dates and the Column A labels?

I've spent a couple of hours trying R1C1 coding but I'm not getting it.

As always, your help is appreciated.

swt

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[SkipVought]

hi,

Simply FILTER the table to display the desired results.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[swtrader]

I use the filter a lot to hide/unhide rows of data based on criteria. However, I don't know how to filter columns.

How would I use a filter to include the ranges A1:A4 (Labels) and C14 (Data, with Dates in R1) based on dates?

If filtering of Columns won't work, I think I can write the code to Hide those date columns that are not in the user-selected range of dates. I hadn't thought of this until you suggested Filtering.

Thank you.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[SkipVought]

Say A2 is 7/1/2012, A3 is 7/2/2012, etc. -- out 2 years.

At various times a user may want to print only 9/1/2012 thru 11/30/2012 or 8/1/2013 thru 8/31/2013....including column A labels.

That says NOTHING about columns!!!

Please state clearly, concisely and completely what your requirements are.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[swtrader]

You're absolutely correct. "Say A2 is 7/1/2012, A3 is 7/2/2012, etc. -- out 2 years." would indicate rows, not columns. It should have been "...B2, C2, D2 have the dates "

My mistake. Whereas you answer dozens, if not hundreds of these over a period of time, I ask only 1 question every few months so I don't have much experience at getting it right. I'll review and re-review my questions in the future to try to make sure they are clear so as not to waste yours or others time.

Thanks for your ever-present help.



swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[SkipVought]

faq68-5184
faq68-5287

Your worksheet data structure is your major problem AND [red]obstacle[/red]! It is a horrendous issue, that, if you maintain this structure, will require CONSTANT ADJUSTMENTS in your formulas as your range of dates (columns) increases.

Given a proper table structure, you solution can be easily and simply accomplished ON THE SHEET, sans VBA, using a Pivot Table report.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[swtrader]

What I am working on is essentially a pivot table. The data is in a flat file...
Date Description Amount 8,000 rows +/-

The Description strings in the raw data are searched for certain key words and the record is then classified as to category. Code then runs to group and sum all category items by each specific date so there is only one dollar value for each date for each category. A unique string is created using a combination of the category and date. The report uses a lookup to match the intersection of date and category on the report to the data table. The report is a rolling cash budget -- which is normally done week by week for a 13 week rolling period. When cash is tight for a small company, the cash manager really needs to see cash projections day by day. The Excel template/report is flexible in that any one amount can be easily re-keyed (which is often necessary as anticipated collections change). Although I use pivot tables in some other work that I do, this Excel structure works well because I often deal with new clients whose bookkeeping staffs have enough experience with Excel to be able to understand the spreadsheet. They would have to be retrained to use a pivot table -- and we're usually under a time crunch -- trying to determine, for example, if there is going to be enough cash to meet payroll next week and next month. More than you wanted to know.....thanks for your guidance and suggestions. Final question, please -- would you suggest Access as a data source for an Excel pivot table? The relational design afforded by Access makes it flexible but its pivot table seems more restrictive than Excel.

 

[SkipVought]

Yes, virtually ANY database source can be the EXTERNAL data source for a PT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Back to your original question regarding printing where certain columns are not visible.

Loop thru the cells in row 1, hiding DATE columns either less than the min date or greater than the max date.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Dim r as range

With YourSheetObject
  For each r in range(.[ai], .[a1].end(xltoright))
    With r
      If isdate(.value) then
        Select case .value
          Case < MinDate
             .entirecolumn.hidden = true
          Case > MaxDate
             .entirecolumn.hidden = true
          Case else
             .entirecolumn.hidden= false
        End select
      End if
    End with
  Next
End with

Skip,
[sub]
[glasses]Just traded in my [b]old subtlety[/b]...
for a [b]NUANCE![/b][tongue][/sub]

 

[swtrader]

Excellent. Very helpful. I could have gotten there after a couple of hours but it would have been clunky at best. And I just ordered an Excel 2007 pivot table book: "Pivot Table Data Crunching for Microsoft Office Excel 2007"
Jelen, Bill
I respect your suggestion on use of PTs. As time permits, I will attempt to migrate the 'applicatiion' to an Access/Excel app. (I have far more experience in Access.) If you know of other (or better) books on the subject, I would appreciate your input.
Thanks again for your time and your wisdom.

 

[SkipVought]

Just remember, when you go to begin this migration, do not simply translate the same data structure. Get a savvy database "guy" to work the design!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[swtrader]

Will do. Good advice.