How Do I Update Visible Rows?

[joel009]

I am able to apply an autofilter to a worksheet, then copy the visible rows to another Workbook (New) just fine. I need to, when I return to the filtered worksheet, add data to a cell outside of the .AutoFilter.Range for only the visible rows. (Col M).

I could loop through the entire worksheet and match on the AutoFilter criteria but that will take longer if/as the sheet grows in size.

I am hoping to find a more direct way.

What I have so far is:

Set rngInvSum = .AutoFilter.Range
                    rngInvSum.Select
                    For lngRow = rngInvSum.row To rngInvSum.Rows.count
                    
                        If Not .currentrow.Hidden Then
                          Range("M" & lngRow) = Right(strNewSheet, Len(strNewSheet) - 10)
                        End If
                    Next

Which is giving me the error:
438 - Object doesn't support this property or method.

Whenever I try to check the visible or hidden property I get an error.

I have tried setting the range object with:

Set rngInvSum = Range("A2").End(xlDown).xlSpecialCells(xlCellTypeVisible)
Set rngInvSum = Range(Range("A1"), Range("A1").End(xlDown)).SpecialCells(xlCellTypeVisible)

with similar results.

Is it possible to do in one operation or am I stuck with looping the entire sheet?

Thanks in Advance to anyone offering ideas.

Joel

 

[SkipVought]

hi,

I don't understand, "add data to a cell outside of the .AutoFilter.Range for only the visible rows. (Col M)."

If your entire table is being filtered (which means that you don't have any EMPTY ROWS in your table) then ONLY the visible rows are, er uh, VISIBLE. Other rows are not in the table. So I do not understand your question.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

Well, I am working in a spreadsheet. I don't understand a few things which I freely admit. I can get the range of the autofiltered spreadsheet, copy it and I have only the visible rows when I paste it to another spreadsheet. When I am done with the paste I need to, in the original spreadsheet, still filtered, add a value to column M for each row visible. The range for autofilter.range is A1:L12 and I want the value in column M. Column M will hold an Invoice number and the filter #. Currenlty splitting the Invoice Summary into 33-35 workbooks.

If I can treat the Range as a table and work with it I need some direction. If I try to loop through the rows in the range I either update only the first row or everything(Visible and Hidden).

If I can update all the visible rows col M at one time I need some direction. This I would prefer. Previous results are identical to using a loop, I either update only the first row or everything(Visible and Hidden).



Joel

 

[SkipVought]

If you can filter on unique value(s), you ought to be able to assign ALL and ANY invoice numbers by way of a lookup formula.

Please explain how you are filtering.

Furthermore it seem extremely strange to me that you are, "Currenlty splitting the Invoice Summary into 33-35 workbooks." WOW!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

Wow? You have no idea Skip. I was hired over the phone, my phone interview was totally based on my understanding of "the 4 types of looping". I received 2 days of training before the individual I was replacing went on a 90 day LOA. Not too unusual except it is a large temp firm and I am supporting the entire Payroll, Acounts Receivable and Cash departments.
I would know how to do this in Access, VB5 or VB6 or Powerbuilder but even though I am using VBA in Excel I find it different. I am further cobbeled by co-workers and management who have been confined to strictly looping through data, storing data in .dat files (comma delim text files named as .dat). The concept of working through VBA on multiple open workbooks is alien to them as well as myself.
Requirements dictate my process. The request is over a year old basically because no one could figure out how to accomplish what I am doing. The existing process is to manually edit 33-35 sheets in a workbook, create 33-35 seperate workbooks by address, in those workbooks we split out seperate Branch numbers to seperate sheets. In the original Summary worksheet I need to add the Invoice number and seperated out sheet number as InvoiceNum_SheetNum (6395120_1) for each row and a copy of the refered to sheet(s).
I have everything working except adding the InvoiceNum_SheetNum to the Summary worksheet. I have been questioned repeatedly about the accuracy of the data because I am using new techniques unused until now. I (feel free to critique my process flow) have taken the exisitng process which produces a summary sheet with subtotals and grand total, copied it to another worksheet(Temp), removed all subtotals and the grand total. I then filter the sheet, copy the filtered data to a new workbook, name the new spreadsheet in the new workbook with the InvoiceNum_SheetNum, delete the rows from the Temp sheet in the original workbook, then go to the Summary sheet, filter the data the same as the Temp and then I want to add the InvoiceNum_SheetNum to the filtered records (this is my block). I will take a look at the VLookup.

The code below wil show a little of how I am filtering, etc. Most has been gleened from this site - I love Tek-Tips!!!

'Copy Invoice Summary sheet and name Temp
    Call CopySheet("InvoiceSummary")
    ActiveSheet.Name = "Temp"

    'Get Last Column # - Used to put Split Sheet Names In Invoice Sheet
    'Find the LAST real column
    intLastCol = ActiveSheet.Cells.Find(What:="*", _
                    SearchDirection:=xlPrevious, _
                    SearchOrder:=xlByColumns).column

'remove all subtotal rows, will recalculate in workbooks
    'get last row
    With ActiveSheet.UsedRange
        lngLastRowTemp = .Rows.count
    End With
    
    With ActiveWorkbook.ActiveSheet
    
        If Not .AutoFilterMode Then
            Selection.AutoFilter Field:=4, Criteria1:=""

            Rows("2:" & lngLastRowTemp).Delete Shift:=xlUp
            'clears all filters
            ActiveSheet.ShowAllData
            
        End If
    End With
    
'sort by Col A and Col B
    Call SortTwoColumns("A:A", "C:C", xlYes)


    With ActiveWorkbook.ActiveSheet
    
        'get last row
        lngLastRowTemp = ActiveSheet.UsedRange.Rows.count

        
        'process Special requirements requests
        Selection.AutoFilter Field:=1, Criteria1:="=9901 S WILCREST DR*"
        Selection.AutoFilter Field:=3, Criteria1:="=*AQ2*"
        
        Set rngTemp = ActiveSheet.AutoFilter.Range
        rngTemp.Copy

        With Workbooks.Add
          With .Sheets(1)
              .Paste
              .UsedRange.EntireColumn.AutoFit
              
              strCustomerName = Trim(Range("A2"))
              strBranch = Trim(Range("B2"))
              strCostCenter = Trim(Range("C2"))
              strWEdate = Format(Trim(Range("G2")), "mmddyyyy")
              
              'set Sub Totals and Grand Totals
              'get last row
                With ActiveSheet.UsedRange
                    lngLastRowNewWB = .Rows.count
                End With
                'set Sub Totals and Grand Totals
                Range("A" & lngLastRowNewWB + 1) = "Subtotal " & strCustomerName
                Range("A" & lngLastRowNewWB + 2) = "SubTotal " & strCostCenter
                
                If lngLastRowNewWB = 2 Then
                    'subtotal values
                    Range("I" & lngLastRowNewWB + 1) = Range("I2")
                    Range("J" & lngLastRowNewWB + 1) = Range("J2")
                    Range("K" & lngLastRowNewWB + 1) = Range("K2")
                    'Grand Total Values
                    Range("I" & lngLastRowNewWB + 2) = Range("I2")
                    Range("J" & lngLastRowNewWB + 2) = Range("J2")
                    Range("K" & lngLastRowNewWB + 2) = Range("K2")
                Else
                    'subtotal values
                    Range("I" & lngLastRowNewWB + 1) = "=Sum(I2:I" & lngLastRowNewWB & ")"
                    Range("J" & lngLastRowNewWB + 1) = "=Sum(J2:J" & lngLastRowNewWB & ")"
                    Range("K" & lngLastRowNewWB + 1) = "=Sum(K2:K" & lngLastRowNewWB & ")"
                    'Grand Total Values
                    Range("I" & lngLastRowNewWB + 2) = "=Sum(I2:I" & lngLastRowNewWB & ")"
                    Range("J" & lngLastRowNewWB + 2) = "=Sum(J2:J" & lngLastRowNewWB & ")"
                    Range("K" & lngLastRowNewWB + 2) = "=Sum(K2:K" & lngLastRowNewWB & ")"
                End If
                
                
          End With

Joel

 

[SkipVought]

I don't have time to thoroughly look thru your last response or code, but I will this evening. I can sympathize!

One general comment. Summaries ought to be assembled from existing source data. I would think that somewhere the data in the summary that you are filtering on, will appear in the source data along with invoice number. There are better ways to do this other than the method you envision.

If you care to respond to one of my FAQs here and I'll get your email address for more in-depth help.


How to use the Watch Window as a Power Programming Tool faq707-4594


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What version of Excel?

Did you find a way to lookup the invoice?

BTW, I very rarely use VLOOKUP, simply because the range of the lookup value must be the left-most column of the table array range, and that does not often fit the reality of the data I lookup and return. So I almost always use INDEX & MATCH.

Furthermore, I am either referencing data in Excel 2007+ Structured Tables or refernecing tables with Named Ranges based on column names, so I rarely am using A1 notation and certainly not ever R1C1 notation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

Excel 2007 primarily but I am told we need compatibibly to 97.

I have been looking at the Index and Match. I have been pulled away to another hot project but am still working on this.

I have seen much about working in spreadsheets and treating the data like a table and using SQL queries to add, update, delete values. So far this tecnique has eluded me and since it is unknown where I work the method itself has been shot down because "it may not be backward compatible". We are already messing with the dll for the calender in windows 7 so ....
I really am interested in learning about using SQL in Excel and treating the sheets, or data in them, like tables. Time is not on my side however as my 90 day contract expires April 30. But whatever I learn I take with me.

Furthermore, I am either referencing data in Excel 2007+ Structured Tables or refernecing tables with Named Ranges based on column names, so I rarely am using A1 notation and certainly not ever R1C1 notation.

I am just starting to work in (create, edit, copy, paste) sheets and workbooks without them having 'focus' so to speak. The norm here is to activate, select before working in the data. "the active WB or sheet needs to be visible to the user so they know the Macro is working". There is little use of Application.ScreenUpdating. Many users actually enjoy a macro that takes 20+ minutes to run as it gives them a break.

With sheets growing to 100K rows and more, speed is becoming an issue so I am incorporating the Application.ScreenUpdating and displaying a message box when the macro is completed.

Thanks for the help and keep those nuggets of knowledge coming.

Joel

 

[Andrzejek]

I am supporting the entire Payroll, Acounts Receivable and Cash departments.

Just curious... Is that all in Excel?

Have fun.

---- Andy

 

[joel009]

Yep, all in Excel. I am trying to convince my managers that Access may be better for handling spreadsheets of 200K or more rows. I am new to Excel VBA so I may be incorrect in that and I am more familiar with Access.

Joel

 

[SkipVought]

A database would be much MUCH better than a workbook!

faq707-4105

You might also look at Named Ranges. If a sheet is structured properly as a table, with one heading row, each heading value unique in that table, in row 1 starting in column A, having no empty rows, you can use a method like this to Create Names from values in the TOP ROW...

Application.DisplayAlerts = FALSE
[i]YourSheetObject[/i].[A1].CurrentRegion.CreateNames True, False, False, False
Application.DisplayAlerts = TRUE

Then these range names can be used in formulas on the sheet or in your code as Range("YourRangeName")

You are wise to reference the sheet object rather than ues the SELECT or ACTIVATE methods which slow your process.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

Banging this around in my head I'm thinking:
If I add another column to the Invoice Summary sheet and include it in the Filtering process it may be easier to update the visible rows?

Joel