| 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 | ||
| 238141 | 2004-09-26 20:04:00 | Hi Liam, I'll have a look at this later today. The problem with the changes you made is that this line... Set TmpRng = Intersect(Rng.SpecialCells(xlCellTypeVisible), Range("J2:H65536"), ActiveSheet.UsedRange) Should read.. (ie J2:J65536 not J2:H65536) Set TmpRng = Intersect(Rng.SpecialCells(xlCellTypeVisible), Range("J2:J65536"), ActiveSheet.UsedRange) |
parry (27) | ||
| 238142 | 2004-09-26 22:16:00 | Hi again, I think I understand what your doing - you have data in columns A:J and your entering in a heading and formulas in K:L, then want to delete rows based on "<=0" in column J (rather than H?). To run the DeleteRows macro you can simply use one line which calls the procedure (ie it automatically runs it) and put this on the end of your Layout macro … 'Call the CleanPrice procedure Call CleanPriceTo join them into one procedure you could simply just add the lines from the other procedure (including the Dim statements). The version below is the combined lines but I have shortened your Layout procedure as its not necessary to select cells to work with them (the macro recorder always selects things). Sub CleanPrice() Dim Rng As Range, TmpRng As Range 'Layout details Range("K1").Value = "Web Des" Range("L1").Value = "Web Price" Range("L2").FormulaR1C1 = "=IF(RC[-2]*0.1>10,RC[-2]*1.1,RC[-2]+10)" Range("K2").FormulaR1C1 = "=RC[-4]&"" (AP-""&RC[-5]&"")""" Range("K2:L2").Copy Range(Range("K3"), Range("K3").SpecialCells(xlLastCell)) Range("A1").Select '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:J65536"), 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) | ||
| 238143 | 2004-09-27 10:47:00 | Hi, Again thanks for your reply. I have some nice errors for you :D sal.neoburn.net sal.neoburn.net If you like I could send you a copy of the file im working on. Kind Regards Liam |
nz_liam (845) | ||
| 238144 | 2004-09-27 11:53:00 | Have you changed the ranges again :D Email to parryishere@yahoo.com.au and I'll take a look. |
parry (27) | ||
| 1 2 3 | |||||