Determine In Vba If Range Is Empty

[AlexAC]

Hello all!
I'm new to the forum and I want to make VBA check if there's an empty cell from Range B to N no metter the column(like ActiveCell.Offset(0, 1)), but i don't want it to check on column A

This is what i got so far, but i don't whant the code to check on column A:

Dim LastRow As Long 'Find the last used row in column B
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Select
End With
ActiveCell.Offset(1, -1).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.HorizontalAlignment = xlLeft

'Move 2 Cells Down
ActiveCell.Offset(2, 0).Select
ActiveWorkbook.Save

End Sub

 

[VoG]

Please explain in words what you want to do. All that selecting is both confusing and unnecessary.

 

[AlexAC]

when you select a cell i want excel to recognize only row from B to N if the cell is empty

 

[VoG]

Maybe like this

If WorksheetFunction.CountA(Cells(ActiveCell.Row,1).Resize(,13)) = 0 Then

But see this http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/

 

[AlexAC]

Here's what i wanna do:
I have a file that does this (when i press Ctrl+Q):
Saves a file to the network, prints a report and saves data to a register (in excel ofcource).
Into this register, VBA finds the todays month "Sheet" (ex: August) and puts the data from the report to the last empty cell.

But when i select in between rows (like range A10:N10 & A12:N12), well the problem comes here..
excel bypasses Range A12 to N12 (from the example) and puts it to A13:N13

I hope i explained with a sense now. It's a bit comlicated i know but if i could show you it would be much easier.

 

[AlexAC]

I found it, this is what i've been looking for:

If Not (IsEmpty((Range("A:N")))) Then
    ActiveCell.Offset(0, 1).EntireRow.Range("B1").Select

But thanks anyway for trying.
PS: this is where i have found the code: http://www.ozgrid.com/forum/showthread.php?t=89794&page=1

 

[Rick Rothstein]

I found it, this is what i've been looking for:

If Not (IsEmpty((Range("A:N")))) Then
    ActiveCell.Offset(0, 1).EntireRow.Range("B1").Select

Are you sure that formula is doing what you want? I get it return False whether the cells are all empty or if one or more have entries in them. Maybe this test might do what I think you want...

If Evaluate("=COUNTA(A:N)") Then

Note that this is a guess as I am not 100% sure what you are actually looking for.

PS: this is where i have found the code: http://www.ozgrid.com/forum/showthread.php?t=89794&page=1

The webpage at that link does not show any formulas even remotely like the one you posted.

 

[AlexAC]

Yeah, that one works too, thanks. Even though i don't get the difference 'cause they both do the same thing:

If Evaluate("=COUNTA(A:N)") Then
And
If Not (IsEmpty((Range("A:N")))) Then

 

[shg]

From Help:

IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.

 

[Rick Rothstein]

Yeah, that one works too, thanks. Even though i don't get the difference 'cause they both do the same thing:

The only way I can think of for that to be true is if it does not matter to your code whether the range is empty or not. Be sure to read shg's message... it is what I was alluding to when I asked "Are you sure that [code line] is doing what you want?"