0

I'm trying to write a function to return the next blank row on a sheet to insert input.

The code is at work so I can only summarize.

I do something like Set rng = Range("Data!A" & row) then return the reference.

Through debugging, I determined the range being created is referencing a valid cell location. However, it is returning "Nothing" instead of an empty Range object. I've gone so far as to set that cell's value to " " (a space) prior to returning the Range and setting it back to "" after.

My question is a two-parter:
(1) why is an empty (cell values) Range reference always returning "Nothing"?
(2) how do I properly reference an empty cell as a valid Range object?

Community
  • 1
  • 1
CheeseMo
  • 167
  • 2
  • 3
  • 12
  • 2
    It would really help to see the actual code... the two things that jump out as possibilities are 1) what is the datatype of "row." If it's also a range, I can see how this would yield a null value as the result of that expression. 2) How about calling the range explicitly from the sheet, like Worksheets("Data").Range(row, 1) – Hambone Jul 11 '14 at 03:02
  • I guess you have `On Error Resume Next` in your code, remove it and you will get more idea why **rng** is **Nothing**. If an object cannot be Set due to an error on right of the equal sign, and with errors suppressed, the object will become **Nothing**. – PatricK Jul 11 '14 at 03:31
  • Sorry about the lack of code, I'll edit the original post with better examples when I can. Logging into this site is blocked at work, so we'll have to make due, unfortunately. As for your questions, row is an int and I'll have to try your suggestion. Though I've tried similar calls. – CheeseMo Jul 11 '14 at 03:34
  • If your `row` is Integer, I hope you have it less than 32768 as the max value for Integer type is 32767. You should use `Long` - the same type as `TypeName(Range("A1").Row)` – PatricK Jul 11 '14 at 03:47
  • @PatriK `row` has been just under 300. Thank you for pointing that out, though. I like to make things as robust as possible, so I'll make sure to change that soon. – CheeseMo Jul 11 '14 at 04:18

2 Answers2

-1

Say we have a range and want to Select the first genuine empty cell in that range

Sub FirstBlank(r2 As Range)
    Dim r1 As Range
    Set r1 = Cells.SpecialCells(xlCellTypeBlanks)
    If Intersect(r1, r2) Is Nothing Then
        MsgBox "there is nothing to find"
    Else
        r2.Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select
    End If
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Here's the algorithm I'm using to find the blank: 1) find the header who's data will never be blank when input. 2) find the end (xlDown) and add one (to that row number). That gives me the row number for the new record. Your suggestion is intriguing for other projects, however. – CheeseMo Jul 11 '14 at 04:30
-2

You have a mistake in the code.

"A"& row = "A 300" (where row=300)

VBA inserts the leading space.

Conor
  • 1