Find next available empty cell/row

[HAWKI]

Hi:
New to VBA...
Need assistance in finding and returning the address of the next
available EMPTY cell/row following my data rows in a worksheet.

e.g. Data currently exists in range A1:F50 and may contain any number
of randomly embedded empty cells within this range.
Essentially, I want to append a new record of data by placing it into
the current worksheet into a row that I leave intentionally blank,
which is between my last row of data and my row of totals. So essentially,
this blank row would be A51:F51 in the above example and I would
have totals in say A52:F54.

After locating the address of this blank line (which of course may change
as new data rows are added) I would want to add in the new data with
my code PLUS retain a blank row between this data and the totals for
use in further append-type operations.

Any assistance that you could provide would be greatly appreciated!
Thx,
HAWKI

[Zack Barresse]

Hello HAWKI,

Your explanation is a little hard to follow. Can you upload an example file? In this file, please show one set of information (preferably on one sheet) the way you have your data, and on another sheet how you want your data to look afterwards.

Also, what would you use to fire this routine? A Button? Worksheet change? Cell change? Selection change?

[Paleo]

Hi Hawky,

try this:



Sub AddRowData()
Dim n As Long
n = Range("A65536").End(xlUp).Row ' this is your totals row
Range("A" & n - 1) = Data ' where Data is the data you want to paste
Range("A" & n).EntireRow.Insert ' this adds a new blank row
End Sub

[HAWKI]

Thanks guys !
Carlos, If I were to add the following lines into your code, would I have the
address of the target row ?

Dim t As Long
t = Range("A" & n - 1) --where this would be inserted after your Range("A" & n-1)

[Paleo]

Hi Hawki,

yes, that code would give you the address of your target row (the blank row just before the totals).

[HAWKI]

Thanks Carlos and firefytr ... sorry I didn't have an example wksheet.
I'm new at using this forum and will have to work through some nuiances of
its use...
Thanks for the quick responses !!!
On another note, how do you find the address of one of the empty cells in say
column A ? e.g. A1:A50 have numeric data in most cells, but a few cells are
empty. How to search the col. and return the address of each empty cell in this
column? The key search criteria here is that the cell is empty.

Thanks again, in advance for any assistance.

[Paleo]

Use this:


Sub EmptyCell()
Dim i As Long, Addr As String
For i = 1 To Range("A65536").End(xlUp).Row
If Range("A" & i) = "" Then
Addr = Addr & " "
End If
Next
MsgBox Addr
End Sub

[HAWKI]

Thanks, again Carlos ... you've been a great help!
I'll mark as Solved...

Regards,
HAWKI

[Paleo]

You are very welcome Hawki!

:beerchug:

[Zack Barresse]

Hi HAWKI,

Glad you solved it!

I wanted to ask, on finding the blank cells, there may be a much faster way of doing this; although it depends on what you are wanting to do with the blank cells. Could you elaborate a little bit on it for us, so as we may provide you with the most efficient solution?

[Paleo]

Hi Zack,

would it be by using ranges? I though on using that but as I didnt know what Hawki needed I have posted that solution.

[Zack Barresse]

Yeah. Your solution is good, Carlos. :yes

And if HAWKI needs the information output such as it is, then I'd stick with your solution. I was thinking something along the lines of SpecialCells (xlcelltypeBlanks). Whenever I can use SpecialCells I do, because it's native to Excel and it's super-fast. :)

[Paleo]

Hi Zack, gee great, I didnt think about SpecialCells, I would solve using this:



Dim Branco As Range, i As Long
For i = 1 To Range("A65536").End(xlUp).Row
If Range("A" & i) = "" Then
set Branco = Union(Branco, Range("A" & i).Address(False, False)
End If
Next i
Branco.EntireRow.Delete ' Supposing the wanted task is to delete it
End If

[Zack Barresse]

We don't even need to loop Carlos. Not if you want to perform the same action to all cells found in the same SpecialCells category. That is what I mean by using SpecialCells native capability. Here is an example, we only need one line to delete the rows of blank cells in column A ...


Sub test()
Range("A1:A" & Range("A65536").End(xlUp).Row).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
End Sub

And to delete ALL rows that are blank in column A, we can shorten even more to ...

Sub test2()
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

[Paleo]

Gee, thats why you are a Master. Great code, thanks. I will start using this logic.

[Zack Barresse]

:o: Thanks Carlos. I'm just another guy.

Like I said though, depending on what the OP is wanting to do with these cells in the range would depict what type of routine I would run on it. For simultaneous actions, definitely use the SpecialCells; although sometimes it's just not that simple.

[HAWKI]

Hey firefytr & Carlos:
My idea was to do some quality checking of data by column. e.g. If, from my form,
I designate a single column and the current range of DATA within my worksheet,
then the search for empty cells in that col. would indicate how much data, I'm
missing of that type since that column represents a certain type of data unique
from other columns. Likewise, I could specify the full range of columns and the
max. range of the DATA in my worksheet and get a report of how much data
(e.g. col. A has 5 missing values, col. F has 3 missing values.). Since DATA
may be a collection of daily data, this would help me evaluate whether my
data capture is working well enough or needs supplementing with manual inputs
to make the corrections.

If you want to submit anything for such a search, then use the criteria provided
previously for worksheet layout as I will not be able to supply a worksheet example.

[Zack Barresse]

If I understand you correctly, you are just looking to count the blank cells in a column range? If so, here is an example of putting that figure into a message box ...


Sub testCountBlanks()
Dim lngCount As Long
lngCount = Range("A1:A100").SpecialCells(xlCellTypeBlanks).Count
MsgBox "There are " & lngCount & " blank cells in A1:A10."
End Sub

Now this does not have error handling in it; in other words it will fail out if there are no blank cells. Plus the message box will read funny if there is only one (blank) in the selection. A more complete version (taking both of those issues into consideration) would look like ...


Sub testCountBlanks()
Dim lngCount As Long
On Error Resume Next
lngCount = Selection.SpecialCells(xlCellTypeBlanks).Count
Select Case lngCount
Case Is > 1
MsgBox "There are " & lngCount & " blank cells in A1:A10."
Case 1
MsgBox "There is one blank cells in A1:A10."
Case Else
MsgBox "There are no blank cells in A1:A10."
End Select
End Sub

[rsilberfarb]

VBA novice needs some clarification.

All I want to do is find the first blank row in a worksheet and then paste something from another wroksheet. Basically make a running list each time moving down one row.

I am pretty sure this is close, but I can't make it work right.

Help Please!!!

[Bob Phillips]

VBA novice needs some clarification.

All I want to do is find the first blank row in a worksheet and then paste something from another wroksheet. Basically make a running list each time moving down one row.

This code snippet copies the activecell row to the next free row on worksheet 2.


With Worksheets("Sheet2")
iLastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.EntireRow.Copy .Cells(iLastRow, "A")
End With

[rsilberfarb]

XLD,


Thanks!!!

That worked for the most part. I had some formulas in the range I was copying and I wanted to only copy the values.

I am sure I did not do this most efficient way, but it works.

Thanks Again!!!

Ross