Forum Home
Press F1
 
Thread ID: 45397 2004-05-21 03:51:00 Making a Excel Macro/Filter nz_liam (845) Press F1
Post ID Timestamp Content User
238131 2004-09-26 05:01:00 Back again with more questions.

ATM I download a suppliers CSV sheet, use an Excel macro to apply some formulas which reformat the data in the sheet. Then I import the CSV into a new Access table and use Access to filter rows where the items have a stock level of <=0, then I delete these offending rows from the access table, and export a clean table back to CSV.

Is it possible to apply a filter to Excel that will look at a column and find values that are "<=0", remove these items, and shift all the cells up?

If I can do this in excel then I can combine it all into a mega-macro and save me about 3 hours work each day.



Kind Regards

Liam 		nz_liam (845)
238132 2004-09-26 07:40:00 Hi Liam, yes thats quite easy .

1 . Data|Filter
2 . Click on drop down arrow for the column in question and select Custom
3 . Change drop down to 'Less than or equal to' and put 0 in the right hand box
4 . Delete the filtered rows

If you have lots of criteria see Advanced Filter . If you advise the range of your data (eg A1:G500) and which column you want to delete <=0 then I will write a macro 4 u . If you decide to write a macro yourself, you will need to use the Visible property or else it may pick up non filtered rows .

hth 		parry (27)
238133 2004-09-26 08:18:00 Heres an example where range is A1:C??? and you want to apply criteria to column C...

Sub DeleteRows()
Dim Rng As Range, TmpRng As Range

'Set a range of target cells
Set Rng = Range("A1:C" & Range("C65536").Row)

'Turn on AutoFilter
Rng.AutoFilter

'Filter the 3rd field (ie column C) with criteria <=0
Rng.AutoFilter 3, "<=0"

'If row 2, field 3 (ie column C) is empty then nothing was found so just remove filter
If IsEmpty(Rng(2, 3)) Then GoTo CleanUp

'Set a range from row 2 down of only visible cells
Set TmpRng = Intersect(Rng.SpecialCells(xlCellTypeVisible), Range("C2:C65536"), ActiveSheet.UsedRange)

'Delete the range - these will be the cells that met the criteria
TmpRng.EntireRow.Delete

CleanUp:
Rng.AutoFilter 'Turn off AutoFilter
Set Rng = Nothing 'Release Rng variable from memory
On Error Resume Next
Set TmpRng = Nothing 'Release TmpRng variable from memory
On Error GoTo 0
End Sub 		parry (27)
238134 2004-09-26 08:22:00 It shouldnt make much diff. but this line...
Set Rng = Range("A1:C" & Range("C65536").Row)

should have read...
Set Rng = Range("A1:C" & Range("C65536").End(xlUp).Row)

This just sets a range from A1 to the last used cell in col C. 		parry (27)
238135 2004-09-26 11:10:00 > It shouldnt make much diff. but this line...
> Set Rng = Range("A1:C" & Range("C65536").Row)
>
> should have read...
> Set Rng = Range("A1:C" &
> Range("C65536").End(xlUp).Row)
>
> This just sets a range from A1 to the last used cell
> in col C.


Hey thanks for the cool macro, unfortunately it doesn’t seem to like my sheet even when I modify the column c to column h.

I have uploaded a preview of my spreadsheet to sal.neoburn.net


Thx for the help.

Liam 		nz_liam (845)
238136 2004-09-26 11:17:00 In the image I uploaded above I would first filter by col-H removing every item with a negative quantity, then I would filter by col-J removing all items with a $0.00 value, this effectively eliminates every item in my spreadsheet which is out of stock, or too new/expensive to have a price yet. Finally I might run a filter to remove any rows with things like "freight" "courier" "shipping" etc in them.


Thanks for all the help.


Liam 		nz_liam (845)
238137 2004-09-26 11:40:00 From y previous example you needed to change references that had 3 to 8 (H=Col # 8) and column C to H. This should hopefully work 4 u...

Sub DeleteRows()
Dim Rng As Range, TmpRng As Range

'Set a range of target cells
Set Rng = Range("A1:H" & Range("H65536").End(xlUp).Row)

'Turn on AutoFilter
Rng.AutoFilter

'Filter the 8th field (ie column H) with criteria <=0
Rng.AutoFilter 8, "<=0"

'If row 2, field 8 (ie column H) is empty then nothing was found so just remove filter
If IsEmpty(Rng(2, 8)) Then GoTo CleanUp

'Set a range from row 2 down of only visible cells
Set TmpRng = Intersect(Rng.SpecialCells(xlCellTypeVisible), Range("H2:H65536"), ActiveSheet.UsedRange)

'Delete the range - these will be the cells that met the criteria
TmpRng.EntireRow.Delete

CleanUp:
Rng.AutoFilter 'Turn off AutoFilter
Set Rng = Nothing 'Release Rng variable from memory
On Error Resume Next
Set TmpRng = Nothing 'Release TmpRng variable from memory
On Error GoTo 0
End Sub 		parry (27)
238138 2004-09-26 12:01:00 Cool, that works a treat.

Now I have two macros:

Sub Layout()
'
' Layout Macro
' Macro recorded 26/09/2004 by TOSHIBA
'

'
Range("K1").Select
ActiveCell.FormulaR1C1 = "Web Des"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Web Price"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]*0.1>10,RC[-2]*1.1,RC[-2]+10)"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=RC[-4] & "" (AP-"" & RC[-5] & "")"""
Range("K2:L2").Select
Selection.Copy
Range("K3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
End Sub

And

Sub DeleteRows()
Dim Rng As Range, TmpRng As Range

' Set a range of target cells
Set Rng = Range("A1:H" & Range("H65536").End(xlUp).Row)

' Turn on AutoFilter
Rng.AutoFilter

' Filter the 8th field (ie column H) with criteria <=0
Rng.AutoFilter 8, "<=0"

' If row 2, field 8 (ie column H) is empty then nothing was found so just remove filter
If IsEmpty(Rng(2, 8)) Then GoTo CleanUp

' Set a range from row 2 down of only visible cells
Set TmpRng = Intersect(Rng.SpecialCells(xlCellTypeVisible), Range("H2:H65536"), ActiveSheet.UsedRange)

' Delete the range - these will be the cells that met the criteria
TmpRng.EntireRow.Delete

CleanUp:
Rng.AutoFilter ' Turn off AutoFilter
Set Rng = Nothing ' Release Rng variable from memory
On Error Resume Next
Set TmpRng = Nothing ' Release TmpRng variable from memory
On Error GoTo 0
End Sub

What do I need to change to combine them into one mega macro?


Cheers

Liam 		nz_liam (845)
238139 2004-09-26 12:09:00 He he, I broke the macro again :D

Sub CleanPrice()
Dim Rng As Range, TmpRng As Range

'Set a range of target cells
Set Rng = Range("A1:J" & Range("J65536").End(xlUp).Row)

'Turn on AutoFilter
Rng.AutoFilter

'Filter the 10th field (ie column J) with criteria =0
Rng.AutoFilter 10, "=0"

'If row 2, field 8 (ie column J) is empty then nothing was found so just remove filter
If IsEmpty(Rng(2, 10)) Then GoTo CleanUp

'Set a range from row 2 down of only visible cells
Set TmpRng = Intersect(Rng.SpecialCells(xlCellTypeVisible), Range("J2:H65536"), ActiveSheet.UsedRange)

'Delete the range - these will be the cells that met the criteria
TmpRng.EntireRow.Delete

CleanUp:
Rng.AutoFilter 'Turn off AutoFilter
Set Rng = Nothing 'Release Rng variable from memory
On Error Resume Next
Set TmpRng = Nothing 'Release TmpRng variable from memory
On Error GoTo 0
End Sub

I get Run-time error '91': Object Variable or width variable not set.

And the debugger points me to line TmpRng.EntireRow.Delete

Cheers

Liam 		nz_liam (845)
238140 2004-09-26 12:27:00 Also once I get Sub CleanPrice() working, what do I need to do to merge it into the super 3 in 1 macro (which already contains the other two macros above).


Thanks heaps for all the help! it’s really appreciated.

Liam 		nz_liam (845)
1 2 3