Delete null rows (including ref formulas)

[emik]

Hi guys,

I have almost no experience with macros in Excel so I hope you'll be able to help me out.

I have an Excel file and what I want to do is look through column A until I come to a null column (or the value is 0) and delete every row after that. This works and clears all the data past 5000 but the problem I am having is if there are 5000 rows, there is data to row 17,000 that might just be " #ref" or formatted with color. So I want my code to completely remove anything after that first null so that when I import the data into Access I don't get all these null columns. The code I am using is as follows:
************************************************

For x = 1 To 65536
c1 = ActiveSheet.Cells(x, 1)
If Trim(c1) = "" Or c1 = "0" Then
ActiveSheet.Cells(x, 1).Select
lastrow = x
Application.Rows.EntireRow.Delete
GoTo delete_finished
End If
Next

delete_finished:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete

*************************************************

I appreciate any help, thank you.
Mike

 

[PHV]

Typed, untested:
With ActiveSheet
For x = 1 To 65536
If Val(Trim(.Cells(x, 1))) = 0 Then
.Range(.Cells(x, 1), .Cells.SpecialCells(xlCellTypeLastCell)).EntireRow.Delete
Exit For
End If
Next
.UsedRange
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

Why loop at all??

..

dim rng as range
application.displayalerts = false
application.screenupdating = false
set rng = range("A1").end(xldown).offset(1)
if rng.row = rows.count then 
    msgbox "No cells to delete!"
    exit sub
end if
range(rng, cells(rows.count, 1)).entirerow.delete
application.displayalerts = true
application.screenupdating = true

NB: untested

This is basically the same as the following steps (you could do with a slightly modified recorded macro)...

Select A1
Press Ctrl + down arrow
Press down arrow
Select entire row of activecell to last row in sheet
Press Ctrl + - (minus) | Entire Row | Ok

You should also check out

http://www.asap-utilities.com,

it has a lot of useful items you can use.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[emik]

Hi,

Unfortunately I'm not having any luck.

I forgot to mention that there are 3 sheets. The reason the recorded macro doesn't work is because I need to find the first null or value = 0, the recorded one just goes to the end of my data.

I tried the code to delete everything after the last desired line but it ends up deleting everything on the spreadsheet.

Any ideas?

 

[emik]

ok got it!

Sheets("data").Select
With ActiveSheet
For x = 1 To 65536
If Trim(.Cells(x, 1)) = "" Or Cells(x, 1) = "0" Then
.Range(.Cells(x, 1), .Cells.SpecialCells(xlCellTypeLastCell)).EntireRow.Delete
Exit For
End If
Next
.UsedRange
End With


Thanks for your help guys!!

 

[PHV]

Why selecting ?
replace this:
Sheets("data").Select
With ActiveSheet
with simply this:
With Sheets("data")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

You can use AutoFilter without looping then ...

Sub testme()
    Dim ws As Worksheet, rng As Range
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set ws = Sheets("Sheet1")
    ws.AutoFilterMode = False
    ws.Range("A:A").AutoFilter Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="0"
    On Error Resume Next
    Set rng = ws.Range("A2", ws.Cells(ws.Rows.Count, 1)).SpecialCells(xlCellTypeVisible)
    If Err <> 0 Then
        MsgBox "There were no cells to delete!"
        Exit Sub
    End If
    Debug.Print ws.Range(rng(1), ws.Cells(ws.Rows.Count, 1)).Address
    ws.AutoFilterMode = False
    ws.Range(rng(1), ws.Cells(ws.Rows.Count, 1)).EntireRow.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

This assumes your sheet name is Sheet1, your data is in column A with data starting in A2.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci