Belch
08-08-2006, 04:14 AM
Hi all,
What I am doing is this:
I import a CSV file to a worksheet "Imported CSV", and then add a header row so I can apply an advanced filter to column F.
I want to apply this filter because I want to only show rows with a unique value in column F.
The filter does this job fine, but I then want to take the rows left showing and put them on another sheet so I can go through each row using a For loop and the Offset command.
e.g.
For n1 = 1 To nTotalRows
sCurrentCode = Range("F1").Offset(n1, 0).Value
...
Next n1
As you can see, using a For loop like this would still select the rows hidden by the advanced filter, which is why I want to have just the rows left showing on their own.
Can anyone help me out with the best way to either:
Remove duplicate rows completely (i.e. not just hide them); or
Use a loop to go through rows that are showing and ignore hidden ones (this is not really efficient though as there are 10000 rows, but only 3000 when filtered so the For loop would be much bigger if it checked every rows 'hidden' property); or finally
Copy the visible rows to another sheet - I have tried this with the ActiveSheets.Paste and Range("A1").PasteSpecial commands - the ActiveSheets one seems to paste the values but then throws a run-time error everytime.I am sure there must be a simple way to do this but I can't seem to get to it!
Any advice is appreciated, thanks.
What I am doing is this:
I import a CSV file to a worksheet "Imported CSV", and then add a header row so I can apply an advanced filter to column F.
I want to apply this filter because I want to only show rows with a unique value in column F.
The filter does this job fine, but I then want to take the rows left showing and put them on another sheet so I can go through each row using a For loop and the Offset command.
e.g.
For n1 = 1 To nTotalRows
sCurrentCode = Range("F1").Offset(n1, 0).Value
...
Next n1
As you can see, using a For loop like this would still select the rows hidden by the advanced filter, which is why I want to have just the rows left showing on their own.
Can anyone help me out with the best way to either:
Remove duplicate rows completely (i.e. not just hide them); or
Use a loop to go through rows that are showing and ignore hidden ones (this is not really efficient though as there are 10000 rows, but only 3000 when filtered so the For loop would be much bigger if it checked every rows 'hidden' property); or finally
Copy the visible rows to another sheet - I have tried this with the ActiveSheets.Paste and Range("A1").PasteSpecial commands - the ActiveSheets one seems to paste the values but then throws a run-time error everytime.I am sure there must be a simple way to do this but I can't seem to get to it!
Any advice is appreciated, thanks.