Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I'm a financial analyst and a very green VBA user. Part of my work

involves spreadsheets I download from various investment web sites.

Usually the spreadsheets have from 50 to 100 rows and have nine

columns. Invariably there are rows where the item for that row isn't

relevant and are therefore filled with zeros. I want to eliminate

all rows that have all zeros in them.

I've taken a shot at this myself but can't get it to do what I need

it to do.

Can someone suggest another approach?

Sub erase_zero_rows()

'

' erase_zero_rows Macro

' Macro recorded 6/19/2007 by

'

Dim iColumnToStart As Integer

Dim iColumnToEnd As Integer

Dim Rng As Range

Dim iRowToStart As Integer

Dim lRowsAll As Integer

Dim iRowIndex As Integer

Dim bNotZero As Boolean

iRowToStart = 4

lRowsAll = 69

iColumnToStart = 2

iColumnToEnd = 4

For Each Rng In Range(Cells(iRowToStart, iColumnToStart), Cells

(lRowsAll, iColumnToEnd))

iRowIndex = 0

bNotZero = False

Do While iRowIndex <= iColumnToEnd

If Rng.Offset(0, iRowIndex).Value <> 0 Then

bNotZero = True

Exit Do

End If

iRowIndex = iRowIndex + 1

Range("a1") = iRowIndex

Loop

If bNotZero = False Then

Rng.EntireRow.Delete shift:=xlUp

End If

Next Rng

End Sub

I've never tried deleting rows, but below is what I use for hiding them;

Sub HideEmpty()

Sheet1.Unprotect

Application.ScreenUpdating = False

BeginRow = 20

EndRow = 1000

ChkCol = 1

For rowcnt = BeginRow To EndRow

If Sheet1.Cells(rowcnt, ChkCol).Value < 1 Then

Sheet1.Cells(rowcnt, ChkCol).EntireRow.Hidden = True

Else

If Sheet1.Cells(rowcnt, ChkCol).Value = "AutoHide.Sheet.Stop"

Then Exit For

Sheet1.Cells(rowcnt, ChkCol).EntireRow.Hidden = False

End If

Next rowcnt

Sheet1.Protect

Application.ScreenUpdating = True

End Sub

your approach is pretty good for someone who says they are

green...

How about trying this...

Sub erase_zero_rows()

Dim rCnt As Integer, iCnt As Integer

Dim iColumnToStart As Integer

Dim iColumnToEnd As Integer

Dim iRowToStart As Integer

Dim iRowsAll As Integer

iRowToStart = 4

iRowsAll = 69

iColumnToStart = 2

iColumnToEnd = 4

iCnt = iRowToStart - 1

rCnt = iRowsAll

With Range(Cells(1, iColumnToStart), Cells(1, iColumnToEnd))

Do While rCnt

If WorksheetFunction.Sum(.Offset(iCnt, 0)) = 0 Then

.Offset(iCnt, 0).EntireRow.Delete

Else

iCnt = iCnt + 1

End If

rCnt = rCnt - 1

Loop

End With

End Sub

do you want to delete the rows where all the cells have zero value...

if the above said is true then find the answer below

Sub OM()

Dim lastrow As Integer

Dim lastcol As Integer

Worksheets("Sheet1").Activate

lastrow = Range("a65536").End(xlUp).Row

lastcol = 9 'because you are sure it is 9

Count = lastrow

i = 1

While (lastrow <> 0)

If (Range("A" & i) = 0 And Range("B" & i) = 0 And Range("C" & i)

= 0 And Range("D" & i) = 0 And Range("E" & i) = 0 And Range("F" & i)

= 0 And Range("G" & i) = 0 And Range("H" & i) = 0 And Range("I" & i)

= 0) Then

Range("A" & i).Select

Selection.EntireRow.Delete shift:=xlUp

End If

i = i + 1

lastrow = lastrow - 1

Wend

End Sub

Let me know if this solves your problem...

Find the answer below

Sub OM()

Dim lastrow As Integer

Dim lastcol As Integer

Worksheets("Sheet1").Activate

lastrow = Range("a65536").End(xlUp).Row

lastcol = 9 'because you are sure it is 9

Count = lastrow

i = 1

While (lastrow <> 0)

If (Range("A" & i) = 0 And Range("B" & i) = 0 And Range("C" & i)

= 0 And Range("D" & i) = 0 And Range("E" & i) = 0 And Range("F" & i)

= 0 And Range("G" & i) = 0 And Range("H" & i) = 0 And Range("I" & i)

= 0) Then

Range("A" & i).Select

Selection.EntireRow.Delete shift:=xlUp

End If

i = i + 1

lastrow = lastrow - 1

Wend

End Sub

You'll also need to stop the row increment, otherwise you bypass any

zero rows that are consecutive.

code ...

While (lastrow <> 0)

If /condition/ Then

code ...

i=i-1

End If

i=i+1

code ...

Wend

I provided would not

work if there were positive and negative numbers on the same row that

could total 0.

Perhaps, then, this will work.

if abs(worksheetfunction.min(.offset(icnt,0))) + worksheetfunction.max

(.offset(icnt,0)) = 0 then

You got me 99.% of the way there. without i = i-1 before the End If it

would skip consecutive zero rows.

Didn't find what you were looking for?
Find more on vba routine for eliminating rows with all zeros from spreadsheet
Or get search suggestion and latest updates.

Related Topics:

- making a spreadsheet cell name a global variable in the VBA project
- Problem implementing spreadsheet containing VBA code in Excel 2007
- How to Sort a Spreadsheet by Alpha and Keep the Row Hierarchy Intact
- Macro to Suppress Rows w/ Zero Value
- Counting the rows If the next row value is same as the previous row
- Summing on varying number of rows in Excel using VBA
- UnHide non-consecutive rows - VBA
- Reference range row and column in VBA
- Insert Rows, based on data in range, using VBA
- Deleting Non-consecutive rows in VBA
- Need Excel VBA Help with deleting rows
- Reference range row and column in VBA
- VBA code to convert all values of a column in a row separated by semi colon
- Excel VBA to delete a row of data
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Convert code from excel 2000 vba to excel 2003 vba
- Placing VBA Code "Live" Into Another WkBook Via VBA
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Eliminate SaveAs capability
- Delete Part of Row and move next Row Down Up
- Move data from a column of row couplets to multiple rows of colum
- Merge a series of rows with the same ID reference to a single row automatically using VBA.
- Updating text in form while routine is running