Hi everybody,

I have the follwoing code which works on going to the first blank cell
in column A ONLY ..

I want to adjust it to go to the first blank cell in the current cloumn
... wether it is A or B or what ever.


Code:
--------------------
Sub Blank()
Cells(Range("A1").CurrentRegion.Rows.Count + 1, 1).Select
End Sub
--------------------


I hope my question is clear.

Thank you for all.


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=486621

Hi LoveCandle,

Try:

Sub Blank()
Cells(1, ActiveCell.Column).End(xlDown)(2).Select
End Sub

---
Regards,
Norman



"LoveCandle" wrote
in message ...

Hi everybody,

I have the follwoing code which works on going to the first blank cell
in column A ONLY ..

I want to adjust it to go to the first blank cell in the current cloumn
. wether it is A or B or what ever.


Code:
--------------------
Sub Blank()
Cells(Range("A1").CurrentRegion.Rows.Count + 1, 1).Select
End Sub
--------------------


I hope my question is clear.

Thank you for all.


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile:
http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=486621

Thank you soooooo much Norman ..

The code is fantastic .. but I had to add something to be working
perfectly,


Code:
--------------------
Sub Blank()
On Error GoTo Blank_Column
Cells(1, ActiveCell.Column).End(xlDown)(2).Select
Blank_Column:
If Err = 1004 Then
MsgBox "There is no data in this column", , "No Data"
End If
End Sub
--------------------


Thank you again Norman


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=486621

Hi LoveCandle,

To additionally allow for data in the first cell of the active column or,
alternatively, for a fully populated column, try:

Public Sub Blank()
Dim rng As Range

Set rng = ActiveCell.EntireColumn.Cells(1)

If IsEmpty(rng) Then GoTo Blank_Column

If IsEmpty(rng(2)) Then
rng(2).Select
Else
On Error GoTo XIT
rng.End(xlDown)(2).Select
On Error GoTo 0
End If

Exit Sub

Blank_Column:
MsgBox "There is no data in the first cell of this column", , "No Data"
Exit Sub

XIT:
MsgBox "The column is full!", , "No room!"

End Sub
'===============

---
Regards,
Norman



"LoveCandle" wrote
in message ...

Thank you soooooo much Norman ..

The code is fantastic .. but I had to add something to be working
perfectly,


Code:
--------------------
Sub Blank()
On Error GoTo Blank_Column
Cells(1, ActiveCell.Column).End(xlDown)(2).Select
Blank_Column:
If Err = 1004 Then
MsgBox "There is no data in this column", , "No Data"
End If
End Sub
--------------------


Thank you again Norman


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile:
http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=486621

"Norman Jones" wrote in message
...

Hello Norman.
Just a similar question.

Given:
A1:A10 Data
A11:A14 Blanks
A15:??? Data
A100 Blank
A101:A200 Data

I use a rather complicated IF function to determine the range
(contiguous data rows) starting from A15:

Set SourceRange = [Sheet3!A15]
If Not IsEmpty(SourceRange(2, 1)) Then
Set SourceRange = SourceRange.Resize(SourceRange. _
End(xlDown).Row - SourceRange.Row + 1)
End If

Anything better from your hat?

Ciao
Bruno

Hi Bruno,

I use a rather complicated IF function to determine the range
(contiguous data rows) starting from A15:

Set SourceRange = [Sheet3!A15]
If Not IsEmpty(SourceRange(2, 1)) Then
Set SourceRange = SourceRange.Resize(SourceRange. _
End(xlDown).Row - SourceRange.Row + 1)
End If


Anything better from your hat?

No better, but slightly different construction - and only because you ask:

Set srcRng = Sheets("Sheet1").Range("A10:A15")

With srcRng
If Not IsEmpty(.Cells(.Count)(2)) Then
Set srcRng = Range(.Cells(1), .End(xlDown))
End If
End With


Saluti.

---
Regards
Norman



"Bruno Campanini" wrote in message
...
"Norman Jones" wrote in message
...

Hello Norman.
Just a similar question.

Given:
A1:A10 Data
A11:A14 Blanks
A15:??? Data
A100 Blank
A101:A200 Data

I use a rather complicated IF function to determine the range
(contiguous data rows) starting from A15:

Set SourceRange = [Sheet3!A15]
If Not IsEmpty(SourceRange(2, 1)) Then
Set SourceRange = SourceRange.Resize(SourceRange. _
End(xlDown).Row - SourceRange.Row + 1)
End If

Anything better from your hat?

Ciao
Bruno

If your data actually holds constant values where you show Data and true
blanks where you show blanks
set rng = Columns(1).SpecialCells(xlConstants)
set SourceRange = rng.Areas(2)

or if all formulas and true blanks, then use xlFormulas
--
Regards,
Tom Ogilvy



"Bruno Campanini" wrote in message
...
"Norman Jones" wrote in message
...

Hello Norman.
Just a similar question.

Given:
A1:A10 Data
A11:A14 Blanks
A15:??? Data
A100 Blank
A101:A200 Data

I use a rather complicated IF function to determine the range
(contiguous data rows) starting from A15:

Set SourceRange = [Sheet3!A15]
If Not IsEmpty(SourceRange(2, 1)) Then
Set SourceRange = SourceRange.Resize(SourceRange. _
End(xlDown).Row - SourceRange.Row + 1)
End If

Anything better from your hat?

Ciao
Bruno

Another one

Sub test()
On Error GoTo BodemUp
Columns("A").Cells.SpecialCells(xlCellTypeBlanks). Cells(1).Select
Exit Sub
BodemUp: Cells(Rows.Count, "A").End(xlUp)(2).Select
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Norman Jones" wrote in message ...
Hi LoveCandle,

To additionally allow for data in the first cell of the active column or, alternatively, for a fully populated column, try:

Public Sub Blank()
Dim rng As Range

Set rng = ActiveCell.EntireColumn.Cells(1)

If IsEmpty(rng) Then GoTo Blank_Column

If IsEmpty(rng(2)) Then
rng(2).Select
Else
On Error GoTo XIT
rng.End(xlDown)(2).Select
On Error GoTo 0
End If

Exit Sub

Blank_Column:
MsgBox "There is no data in the first cell of this column", , "No Data"
Exit Sub

XIT:
MsgBox "The column is full!", , "No room!"

End Sub
'===============

---
Regards,
Norman



"LoveCandle" wrote in message
...

Thank you soooooo much Norman ..

The code is fantastic .. but I had to add something to be working
perfectly,


Code:
--------------------
Sub Blank()
On Error GoTo Blank_Column
Cells(1, ActiveCell.Column).End(xlDown)(2).Select
Blank_Column:
If Err = 1004 Then
MsgBox "There is no data in this column", , "No Data"
End If
End Sub
--------------------


Thank you again Norman


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=486621