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

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

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

"Tom Ogilvy" wrote in message
...
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

Ok Tom.
But my example was only an example.
I could have only one Area in the column.
More than one Area and ignore what area my range is in.
I could have a range made by one cell only.
My IF clause covers all these situations with only one
exception:
Set SourceRange = [V15],
when [V15] is blank and [V16] is not.
You can cover it simply extending the IF clause.
But you must choose what to do then.

Ciao
Bruno

I generally write algorithms to solve specific problems. If you don't know
what the problem is, then I am afraid I can't help. Please state that in
the problem statement so others don't waste their time.

--
Regards,
Tom Ogilvy



"Bruno Campanini" wrote in message
...
"Tom Ogilvy" wrote in message
...
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

Ok Tom.
But my example was only an example.
I could have only one Area in the column.
More than one Area and ignore what area my range is in.
I could have a range made by one cell only.
My IF clause covers all these situations with only one
exception:
Set SourceRange = [V15],
when [V15] is blank and [V16] is not.
You can cover it simply extending the IF clause.
But you must choose what to do then.

Ciao
Bruno

"Tom Ogilvy" wrote in message
...
I generally write algorithms to solve specific problems. If you don't know
what the problem is, then I am afraid I can't help. Please state that in
the problem statement so others don't waste their time.

I don't need your help and I don't want to waste your time,
then stop reading; it's no matter for you.

It was a question I specifically put to Norman Jones
and he understood it quite ok.
BTW, once again the question; just in case my english
has been not clear enough.

Given a cell address (say W34),
is there a less complicated way to define a range as a number
of non-blank contiguous cells starting from W34 down?
A way covering all possible cases, without any ambiguity?

The "complicated way" I'm using is the following:

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

Bruno

Thank you all for the worthy help,


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