Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro to remove data where value is 0
I currently have a speadsheet which has been set up with
various heading across the page and reference numbers as data down the first column. When goods have been received, a value is entered in the column heading and against the ref number.The sheet is updated daily At the end of the week I am looking to condence the information to only show data with values. The filter cannot work - Can you help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro to remove data where value is 0
Sub DeleteemptyRows()
Dim rng as Range, rng1 as Range Dim LastRow as Long set rng = cells(2,2) lastrow = cells(rows.count,1).End(xlup) do while rng.row <= Lastrow if application.countA(rng.Resize(1,255)) = 0 then if rng1 is nothing then set rng1 = rng Else set rng1 = Union(rng1,rng) end if end if set rng = rng.Offset(1,0) Loop if not rng1 is nothing then rng1.EntireRow.Delete End if End sub Would be one interpretation of what you describe. -- Regards, Tom Ogilvy Paul wrote in message ... I currently have a speadsheet which has been set up with various heading across the page and reference numbers as data down the first column. When goods have been received, a value is entered in the column heading and against the ref number.The sheet is updated daily At the end of the week I am looking to condence the information to only show data with values. The filter cannot work - Can you help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro to remove data where value is 0
I read the problem differently from Tom and am kinda simple.
dim i as integer dim lastrow as integer lastrow = cells(rows.count,1).End(xlup) While i <= lastrow If amt + Sheet1.Columns("B").Rows(i).Value< 0 Then i = i + 1 'set i to next row Else 'delete row Rows(i & ":" & i).Select Selection.ClearContents Selection.Delete Shift:=xlUp End If Wen -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro to remove data where value is 0
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping Macro | Excel Discussion (Misc queries) | |||
Looping Macro | Excel Discussion (Misc queries) | |||
Looping macro | Excel Worksheet Functions | |||
Looping a macro | Excel Discussion (Misc queries) | |||
macro to browse for workbook, pick up data and looping | Excel Worksheet Functions |