PDA

View Full Version : Selecting rows in viewable filters



j.smith1981
03-30-2009, 01:16 AM
I dont know if the above title gives this problem justice, ah well here goes.

I have written a double loop macro, sadly we couldnt get the FIND function on this sites tutorial working, so we opted for a loop that returns a filtered list of products.

Is there anyway of making the macro select those viewable items ie rows not columns, rather than selecting the entire list of products including hidden ones?

I am a bit stuck with this and would appreciate any help available.

Just for a note, the macro I have written, loops down the products excel file (workbook), when it gets to the next productcode (regardless of any data in it), it then switches to the data excel workbook, checks to see if the product code exists in there in the instr command, when it is found, it offsets to copy 2 fields into the products list and repeats the cylce, the products list has around 716 items in it for importing into our ecommerce site, and the data file has 9,600+ items in it, so I think you can see how long this would take, filtering these results seems easier to us and much quicker.

I have been looking at this example: http://vbadud.blogspot.com/2008/08/retrieve-get-first-row-of-excel.html

Seems like it could be the Intersect command that allows this but I am not sure how to actually use it, more amend it to my code which stands as:

Sub subCategoryMaker1()
Application.ScreenUpdating = True

Application.DisplayAlerts = False


Dim fileLoc As String

Dim lastCSV As String

Dim productcode As String

Dim cat As String
Dim subcat As String



fileLoc = ThisWorkbook.Path
'------------------------------------------------------------------------------------

Cells(21, 5).Select 'selects E21 (last CSV processed)
lastCSV = ActiveCell.Value 'Saves value as variable

Application.Workbooks.Open (fileLoc & "\" & lastCSV) ' Opens the last generated CSV file

Application.Workbooks.Open (fileLoc & "\" & "dataSheet.xls") ' Opens the data workbook

Application.Workbooks(lastCSV).Activate

'Do loop here? to stop the macro going through all manufacturers on x ref data.xls


Cells(3, 2).Select
countnumber = 1

Do 'While IsEmpty(ActiveCell.Offset(0, 6)) = False ' Loops until current cell is empty?

productcode = ActiveCell.Value 'Saves the current cell value as variable
manufacturer = ActiveCell.Offset(0, 6).Value


Application.Workbooks("dataSheet.xls").Activate 'Activates data sheet
Sheets("subCategories2").Select

'Cells(2, 4).Select

Selection.AutoFilter
ActiveSheet.Range("$A$1:$D$9560").AutoFilter Field:=4, Criteria1:="=*" & productcode & "*", Operator:=xlAnd

Dim rRange As Range
'Set rRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
Set rRange = ActiveSheet.Range("$A$1:$D$9560").Cells.SpecialCells(xlCellTypeVisible)

'Set rRange = ActiveSheet.AutoFilter.Range

newcounttest = 0
'Do
'If ActiveCell.Formula Like productcode Then 'whole cell value must be the same!
' If InStr(1, ActiveCell.Formula, productcode) Then

' ActiveCell.Offset(0, -2).Select
' cat = ActiveCell.Value

' ActiveCell.Offset(0, 1).Select

' subcat = ActiveCell.Value

' ActiveCell.Offset(0, 1).Select

' Application.Workbooks(lastCSV).Activate 'Activates last products CSV file

' ActiveCell.Offset(1, 0).Select

' ActiveCell.EntireRow.Insert Shift:=xlDown

' ActiveCell.Offset(0, 6).Select

' ActiveCell.Formula = cat & "/" & subcat

' ActiveCell.Offset(0, -6).Select

' Application.Workbooks("dataSheet.xls").Activate

'End If

'ActiveCell.Offset(1, 0).Select
'Loop Until IsEmpty(ActiveCell.Offset(0, 0))
' newcounttest = newcounttest + 1
'Loop Until IsEmpty(ActiveCell.Offset(0, 0))

'For lngRow = 1 To rRange.Rows.Count
' If rRange.Cells(lngRow, 4).Value <> "" Then
' newcounttest = newcounttest + 1
' End If
' Next lngRow

MsgBox rRange.Rows.Count

'MsgBox newcounttest

Application.Workbooks(lastCSV).Activate
ActiveCell.Offset(1, 0).Select 'Happens after the current productcode has been found/not found

Loop Until Selection.Row > countnumber

Cells(1, 1).Select

End Sub



Thanks,

Jeremy.

Bob Phillips
03-30-2009, 05:25 AM
Why don't you post a workbook.

I can't see why Find didn't work for you, but I would use AutoFilter and then SpecialCells(xlCellTypeVisible).

j.smith1981
03-30-2009, 07:35 AM
I am still struggling as to make the macro select the viewable rows and nothing else, can someone help?

The link I have put in makes sense but I am attempting to implement what they have put with the Intersect command but it keeps failing.

Thanks,
Jez