Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Null Rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Null Rows
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. "J. Trucking" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Null Rows
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" wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Null Rows
Thanks for the responses. I'll give this a try.
John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Null/Blank Rows | Excel Discussion (Misc queries) | |||
Deleting Cells where content equals "0" or null | Excel Programming | |||
Deleting Null Cells | Excel Discussion (Misc queries) | |||
How do I pick up only the rows with a non-null key column on a separate sheet? | Excel Worksheet Functions | |||
Global hiding null rows | Excel Discussion (Misc queries) |