Deleting Null Rows

[J. Trucking]

Hello,

I am exporting data from Access to Excel. This part works great. I
have the values exported to a worksheet. I then link those cells to a
formatted worksheet within the workbook. Since the data from Access
is dynamic, I don't always fill up the formatted sheet. Therefore, I
would like to delete any blank rows within the range of rows 9-150 on
the formatted sheet when the workbook is poened. I've used a "Delete
Row" macro before but for the life of me, can't figure out how to do
this again. I would like a macro to look for blank values in column A
between rows 9 and 150. I will be looking for blank values in column
A to determine whether the row stays or gets deleted. I have an IF
statement for the value of the cell which states that if the imported
data (on the seperate sheet) is blank, then the value of the cell will
be blank.

One thing I tried to do is select the range A9:A150. I then pushed F5
and tried to search on special and blanks. Even though some of the
cells were blank (based on the if statement), the results said there
were no blank cells.

Would anyone have any idea how to write a macro to delete rows if the
results of the formula are null?

As well, I have one additional question (it's a little bit Access
related). When I export the data from Access to Excel, I use a form.
There is a "start date" and an "end date" field on the form. I would
like these values to appear in specific cells within the Excel
Workbook. Any Ideas for this one?

Thanks in advance for any help,

John

 

[NateBuckley]

Hello, try this

Sub deleteABlankRow()
Dim c As Range

For Each c In Range("A9:A150")
If c.Value = "" Or c.Value = Null Then
c.EntireRow.Delete xlUp
End If
Next c
End Sub

If this doesn't work (as I'm unsure about the Null keyword in VBA), then you
could try adding within the IF statement

If c.Value = "" or c.Value = null OR len(c.value) = 0 then

All this does is go through Cells A9 to A150, checking if they contain any
blanks or nulls and if they do - delete the entireRow.

Not done much stuff with forms in Access and Excel so I don't want to help
out there just incase I give out wrong information. I hope that code snippet
helps though.

 

[Dave Peterson]

Dim iRow as long
with activesheet
for irow = 150 to 9 step -1
if trim(.cells(irow,"A").value) = "" then
.rows(irow).delete
end if
next irow
end with

=======

If you're positive that you don't have anything in those cells (no space
characters, no whitespace at all)...

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

=========
then you can use the F5|Special|Blanks and delete the rows that way.

 

[J. Trucking]

Thanks for the responses. I'll give this a try.

John