Finding first blank cell in a worksheet

[kafka]

I have a worksheet which is linked to a second worksheet within a workbook by a formula which appears in each cell, i.e.

IF(TOTAL!V922="","",TOTAL!K922)

<tbody>
</tbody>

This means that if there is no data in the cell in the second worksheet the cell in the first worksheet appears to be blank but, in fact, contains the aforementioned formula.

I want to be able, while recording a macro, to find the first of these apparently blank cells so that I can delete all the following rows which are also bank leaving only rows which are populated. I am fairly sure that I was able to do this fifteen years ago but I can't remember how I did it.

 

[mick0005]

This assumes that you have some column headers because it uses the autofilter function to perform this action. This will delete all rows that are empty or which have "" as the result of a formula.

It also assumes that the formula is located in column A. If it isn't you need to modify the field number accordingly.

This is one of the macros I keep in my personal workbook. I got the original code from Excel: Delete/Deleting Blank Rows With Excel VBA

Put this into a standard module.

Sub DeleteRowsBasedOnCriteria()

'Assumes the list has a heading.


    With ActiveSheet


             If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter


                    .Range("A1").AutoFilter Field:=1, Criteria1:="" 'change the field number for your application


                    .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete


            .AutoFilterMode = False


    End With


End Sub

 

[kafka]

Thanks for that. I am not sure where to put this. Do I add it to the existing macro (I tried without success)? Below is my macro with your bit at the end.


Sub Process_Sold()
'
' Process_Sold Macro
'


'
Workbooks("EBAY SALES.xlsx").Connections
Workbooks.Open Filename:="D:\EBAY\EBAY SALES.xlsx", UpdateLinks:=0
Sheets("WORK").Select
Range("A2:I4999").Select
ActiveWorkbook.Worksheets("WORK").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("WORK").Sort.SortFields.Add Key:=Range("A2:A4999") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("WORK").Sort
.SetRange Range("A1:I4999")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ChDir "D:\EBAY"
ActiveWorkbook.SaveAs Filename:="D:\EBAY\EBAY SALES ALPHA.xlsm", FileFormat _
:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Range("A2").Select
End Sub
Sub DeleteRowsBasedOnCriteria()
With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=""
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub

Below are a few lines from the worksheet. In this sample I would want all the rows below Caravelles to be deleted

Artist	Title	Format	POST AU	POST	START	Start	Sold
Mind Expanders	What's Happening?	LP	$8.00	$18.00	26/10/2010	$30.00	$30.00
Caravelles	You Don't Have To Be A Baby	LP	$8.00	$18.00	6/11/2010	$25.00	$25.00

<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[mick0005]

Please remember to put your macro code in using the Code button in your toolbar for proper formatting.

I didn't look through this that carefully to see what the rest of your code is doing, but you need to remove the lines "End Sub" and "Sub DeleteRowsBasedOnCriteria()" from where your code and mine meet. You don't want the application to end until it runs through allcode. Otherwise, it is just stopping and then you will need to initiate my code with some action.

 

[kafka]

Thanks again. I get a Run time Error "Cannot use that command on overlapping selections" in the highlighted line below.

Sub Process_Sold()
'
' Process_Sold Macro
'


'
Workbooks.Open Filename:="D:\EBAY\EBAY SALES.xlsx", UpdateLinks:=0
Sheets("WORK").Select
Range("A2:I4999").Select
ActiveWorkbook.Worksheets("WORK").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("WORK").Sort.SortFields.Add Key:=Range("A2:A4999") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("WORK").Sort
.SetRange Range("A1:I4999")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ChDir "D:\EBAY"
ActiveWorkbook.SaveAs Filename:="D:\EBAY\EBAY SALES ALPHA.xlsm", FileFormat _
:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Range("A2").Select
With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=""
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub

Please remember to put your macro code in using the Code button in your toolbar for proper formatting.

I didn't look through this that carefully to see what the rest of your code is doing, but you need to remove the lines "End Sub" and "Sub DeleteRowsBasedOnCriteria()" from where your code and mine meet. You don't want the application to end until it runs through allcode. Otherwise, it is just stopping and then you will need to initiate my code with some action.

 

[mick0005]

What are you trying to do...sort the data, save the worksheet, then delete the blank rows?

Can you put my code into its own module and try to run it separately? Does my code work then on its own?

 

[kafka]

I have taken a different approach to producing the report I want. I now want to 1. find either the last cell not containing data or the first cell containg data so that I can delete all the empty rows and then 2. having done that, find the last cell containg data so that I can highlight all the rows containg data and set the print area, all in the one macro (although I might make the print are/print part a separate macro.

Below is a bit of the worksheet produced by my macro so far (I don't expect you to do all my work for me, just a suggestion as to where I could insert the lines relating to finding the first empty cell and last occupied cell would be a great help):

4382
4383
4384
4385
4386
4387	4 Instants	Discotheque	LP	$8.00	$18.00	1/11/2013	$15.00	$20.00
4388	4 Instants	Discotheque (2nd copy)	LP	$8.00	$18.00	5/11/2013	$18.00	$18.00
4389	aaaaaaaaaaaaa	bbbbbbbbbbbbbbbb	cd	$1.00	$1.00	3/06/2015	$1.00	$100.00
4390	Alan Dale & Houserockers	Crackin' Up	LP	$8.00	$18.00	31/07/2014	$16.00	$16.00
4391	Alan Freed	And This Is Rock'n'Roll	LP	$8.00	$19.00	15/02/2015	$15.00	$15.00
4392	Alan Price	O Lucky Man	CD	$4.00	$8.00	25/03/2012	$10.00	$10.00
4393	Allusions	Anthology 1966-1968	CD	$4.00	$8.00	1/03/2015	$20.00	$20.00

<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

The macro to do this is:

Sub Process_Sold()
'
' Process_Sold Macro
'


'
Workbooks.Open Filename:="D:\EBAY\EBAY SALES.xlsm", UpdateLinks:=0
Sheets("WORK").Select
Selection.Copy
Sheets("WORK2").Select
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("WORK2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("WORK2").Sort.SortFields.Add Key:=Range("A2:A4999") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("WORK2").Sort.SortFields.Add Key:=Range("B2:B4999") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("WORK2").Sort.SortFields.Add Key:=Range("F2:F4999") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("WORK2").Sort
.SetRange Range("A1:J4999")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

 

[mick0005]

Do you have a predictable number of rows that would be relevant? If so, why not just use a simple Delete All Empty Rows macro for a specific range? No need to find the first row of data, etc... just delete all empty rows from within a range (Row1:Row5000) for example, and it will shift all data up to the top.

Once you have done that, it is easy enough to set the range for where data does exist and you can set the print area.

From Excel: Delete/Deleting Blank Rows With Excel VBA and then I just made the tweak for the number of rows...

By the way, when I want to combine several actions into a single macro, frequently I will just make them as seperate subs and then call the subs into the end of another macro. I like to do this to easily comment out the call command if I dont want them to run.

Just use Call DeleteBlankRows1 and it will run that from within another sub.

Sub DeleteBlankRows1()

'Deletes the entire row within the selection if the ENTIRE row contains no data.


'We use Long in case they have over 32,767 rows selected.


Dim i As Long






    'We turn off calculation and screenupdating to speed up the macro.


    With Application


        .Calculation = xlCalculationManual


        .ScreenUpdating = False


Rows("1:5000").EntireRow.Select 'Change the number of rows according to your need


    'We work backwards because we are deleting rows.


    For i = Selection.Rows.Count To 1 Step -1


        If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then


            Selection.Rows(i).EntireRow.Delete


        End If


    Next i




        .Calculation = xlCalculationAutomatic


        .ScreenUpdating = True


    End With


End Sub

 

[kafka]

I have spent a lot of time fiddling with this. The end result is that, even though my worksheet contains cells and rows which appear to be empty, Excel can't find them. I have tried suggestions for finding blank cells but either they aren't selected or I am told that no cells have been found. I assume that cells which have a border but no content are still considered blank?

This project is not terribly important, more of a mental challenge, so don't feel obliged to spend time on it. I would be interested to know why apparently blank cells aren't recognized as blank by Excel.

 

[kafka]

I have spent a lot of time fiddling with this. The end result is that, even though my worksheet contains cells and rows which appear to be empty, Excel can't find them. I have tried suggestions for finding blank cells but either they aren't selected or I am told that no cells have been found. I assume that cells which have a border but no content are still considered blank?

This project is not terribly important, more of a mental challenge, so don't feel obliged to spend time on it. I would be interested to know why apparently blank cells aren't recognized as blank by Excel.

i have solved the problem by taking a completely different approach. it is not ideal, but it works. In my main worksheet I have entered zzzzz in every empty cell which does not yet have data in it. this means that later on I don't have to find the first empty cell (which was causing me so much trouble) but my macro searches for the first cell containing zzzzz, selects the cell at the end of that row, then sets the print area accordingly. As I said, this is not ideal because having zzzzz in all empty cells in Column A down to row 5000 is not neat, however it works!