Writing visible records (2002/SP-2)

Topic

New Reply

User interacts with a “Master” sheet (database type layout) using auto filters to limit number of records. User then wants to create “Assignment” workbooks with no more than 40 records each based only on the filtered records from the “Master”; a filtered list of 160 records would create 4 separate “Assignment XXX” workbooks, for example.

Problem: How to loop through only visible cells. My current approach is very ham-handed – copying only visible cells to another workbook and then looping through that new workbook to create each of the Assignment sheets. I would think there must be a way to avoid that intermediate step.

BTW, I am couple of years removed from what limited VBA skills I once had, and I must have searched this site 10 times over the last several days to get answers to other questions on this project. I really appreciate what a resource this Lounge is.

Reply | Quote

Replies

You can set a range to get only the visible cells:

Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)

Then you can loop thru each area of the range and each row of the area to do what you want

For Each rArea In rng.Areas
For x = 1 To rArea.Rows.Count
‘your code here
Next
Next

This is general, Let me know if you need additional details.

Steve

Reply | Quote

Steve and John –

I think this is exactly what I needed. Thanks!

Bill

Reply | Quote

Steve and John –

I think this is exactly what I needed. Thanks!

Bill

Reply | Quote

Steve –

Was not able to figure out how to use your suggestion when the visible area covered hidden columns as well as rows. I could never tell for sure where I was as the loops were executing since one area might be to the right of the one previous; then the next one would be below and to the left. Doing something wrong probably.

Ended up refining my “ham-handed” approach by adding some error checking and replacing many of my select statements with activate. A little awkward – and I’m sure if I knew what I was doing I could even clean that up more – but it is working.

Thanks for the help. I’m sure I’ll be able to use those concepts in another setting.

Reply | Quote

If you only want to test items in a particular column, you can intersect the “set rng” with the column, then when you go thru the list, you only have go thru the rows (for example)

Set rng = intersect(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible),Range(“A:A”))

to get all the visible cells in the used range in col A.

Steve

Reply | Quote

I think I understand. The problem I would still have is writing a complete (visible) record to another workbook when several nonadjacent columns are hidden, and the macro would not know prior to runtime which columns are hidden.

But again – you’ve introduced me to “intersect” – another concept that I know will come in handy – THANKS.

Reply | Quote

It should not be an issue with hidden columns. As you loop thru the rows you can loop thru each cell of the row and “transfer” any cell whose column is visible

Could you provide a sample sheet with some hidden columns and a filter and explain what you want the macro to do (show the final output)?

Steve

Reply | Quote

Here is the sample. Prior to macro execution user filters records down to a subset that s/he then wants to assign to others to work.

Macro would need to…
> Prompt user for an “Assignment Name”
> Colapse MasterFile column outline levels (if user has not already done so) to make sure only assignment-related columns are visible
> Loop to…
>>> Open “Assignment_Blank.xls” workbook
>>> Write 40 visible records to Assignment_Blank.xls
>>>>>> Assign each visible record an Assign_Date (= AssignmentName_CurrentDate_AssignmentRecord#; where Assignment Record# = 1 thru X number of visible records)
>>>>>> Assign_Date will also be written to the Assignment_Blank.xls
>>> SaveAs (Assignment_Blank.xls) as AssignmentName_N.xls (where N is a sequence incremented with each new file)
>>> Close AssignmentName_N.xls
>>> If more visible records on MasterFile then loop

I currently have all the above working. BUT my version uses a “Selection.SpecialCells(xlCellTypeVisible).Select” command on the MasterFile and then copies and pastes that into a new “temp” workbook. I then process the “temp” workbook: sending the calculated Assign_Date back to the MasterFile; sending 40 records at a time to my AssignmentBlank.xls; using “Application.GetSaveAsFilename” (including a calculated InitialFilename) to save (and then close) each 40-record assignment file; until I run out of records. I then close the “temp” file. My macro is in it’s own workbook – with a lot of other project-related file manipulation procedures – so is not included with this MasterFile.

The sample MasteFile I attached has 45 records – which would mean one Assignment file with 40 records and a second one with only 5.

Steve, I am very interested in finding out the right way to do this (or a better one), but the version I created is working – so I’d hate for you to spend a lot of time picking through this mess if you have better things to do .

Reply | Quote

Here is the first Assignment file my macro created also

Reply | Quote

Here is the first Assignment file my macro created also

Reply | Quote

I don’t have time to do too much it now, but to answer your original question try some code like this:

    Dim wkb As Workbook
    Dim wksMFT As Worksheet
    Dim wksAT As Worksheet
'other DIMs

    Set wkb = Workbooks("Woodys1_001-040.xls")
    Set wksMFT = Worksheets("MasterFileTable")
    Set wksAT = wkb.Worksheets("AssignmentTable")
'other code

    wksMFT.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        wksAT.Range("a1")

'other code
    Set wksMFT = Nothing
    Set wksAT =Nothing
    Set wkb = nothing

You don’t have to worry about areas or looping if you are just looking at copying the entire filtered range.

Steve

Reply | Quote

I don’t have time to do too much it now, but to answer your original question try some code like this:

    Dim wkb As Workbook
    Dim wksMFT As Worksheet
    Dim wksAT As Worksheet
'other DIMs

    Set wkb = Workbooks("Woodys1_001-040.xls")
    Set wksMFT = Worksheets("MasterFileTable")
    Set wksAT = wkb.Worksheets("AssignmentTable")
'other code

    wksMFT.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        wksAT.Range("a1")

'other code
    Set wksMFT = Nothing
    Set wksAT =Nothing
    Set wkb = nothing

You don’t have to worry about areas or looping if you are just looking at copying the entire filtered range.

Steve

Reply | Quote

Here is the sample. Prior to macro execution user filters records down to a subset that s/he then wants to assign to others to work.

Macro would need to…
> Prompt user for an “Assignment Name”
> Colapse MasterFile column outline levels (if user has not already done so) to make sure only assignment-related columns are visible
> Loop to…
>>> Open “Assignment_Blank.xls” workbook
>>> Write 40 visible records to Assignment_Blank.xls
>>>>>> Assign each visible record an Assign_Date (= AssignmentName_CurrentDate_AssignmentRecord#; where Assignment Record# = 1 thru X number of visible records)
>>>>>> Assign_Date will also be written to the Assignment_Blank.xls
>>> SaveAs (Assignment_Blank.xls) as AssignmentName_N.xls (where N is a sequence incremented with each new file)
>>> Close AssignmentName_N.xls
>>> If more visible records on MasterFile then loop

I currently have all the above working. BUT my version uses a “Selection.SpecialCells(xlCellTypeVisible).Select” command on the MasterFile and then copies and pastes that into a new “temp” workbook. I then process the “temp” workbook: sending the calculated Assign_Date back to the MasterFile; sending 40 records at a time to my AssignmentBlank.xls; using “Application.GetSaveAsFilename” (including a calculated InitialFilename) to save (and then close) each 40-record assignment file; until I run out of records. I then close the “temp” file. My macro is in it’s own workbook – with a lot of other project-related file manipulation procedures – so is not included with this MasterFile.

The sample MasteFile I attached has 45 records – which would mean one Assignment file with 40 records and a second one with only 5.

Steve, I am very interested in finding out the right way to do this (or a better one), but the version I created is working – so I’d hate for you to spend a lot of time picking through this mess if you have better things to do .

Reply | Quote

It should not be an issue with hidden columns. As you loop thru the rows you can loop thru each cell of the row and “transfer” any cell whose column is visible

Could you provide a sample sheet with some hidden columns and a filter and explain what you want the macro to do (show the final output)?

Steve

Reply | Quote

I think I understand. The problem I would still have is writing a complete (visible) record to another workbook when several nonadjacent columns are hidden, and the macro would not know prior to runtime which columns are hidden.

But again – you’ve introduced me to “intersect” – another concept that I know will come in handy – THANKS.

Reply | Quote

If you only want to test items in a particular column, you can intersect the “set rng” with the column, then when you go thru the list, you only have go thru the rows (for example)

Set rng = intersect(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible),Range(“A:A”))

to get all the visible cells in the used range in col A.

Steve

Reply | Quote

Steve –

Was not able to figure out how to use your suggestion when the visible area covered hidden columns as well as rows. I could never tell for sure where I was as the loops were executing since one area might be to the right of the one previous; then the next one would be below and to the left. Doing something wrong probably.

Ended up refining my “ham-handed” approach by adding some error checking and replacing many of my select statements with activate. A little awkward – and I’m sure if I knew what I was doing I could even clean that up more – but it is working.

Thanks for the help. I’m sure I’ll be able to use those concepts in another setting.

Reply | Quote

You can set a range to get only the visible cells:

Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)

Then you can loop thru each area of the range and each row of the area to do what you want

For Each rArea In rng.Areas
For x = 1 To rArea.Rows.Count
‘your code here
Next
Next

This is general, Let me know if you need additional details.

Steve

Reply | Quote

This comes up a lot, but Searching for it isn’t easy because the Post wording is not necessarily close to the topic. Have a look at this thread, where SammyB shows how to use AutoFilter in VBA and LegareColeman shows how to code the same outcome without using AutoFilter. If you need more specific assistance, post a censored workbook example showing what your data and filter criteria look like.

Reply | Quote

This comes up a lot, but Searching for it isn’t easy because the Post wording is not necessarily close to the topic. Have a look at this thread, where SammyB shows how to use AutoFilter in VBA and LegareColeman shows how to code the same outcome without using AutoFilter. If you need more specific assistance, post a censored workbook example showing what your data and filter criteria look like.

Reply | Quote