find next blank column in set area
 

Hi,
I have a table from cells O8:Z18 and I want a macro that will find the next
empty column in this area and paste data.

Everything I have tried, finds the next empty column that is outside this
area, does anyone know how I do this?

find next blank column in set area
 

Hi Kirsty,

I have assumed that it is the first empty column within the range O8:Z18 and
not necessarily the entire column in the worksheet which is empty. Not sure
of your experience with VBA but just in case, when you assign a range to a
range variable then the cells actually start from Cells(1,1) (ie.Row 1, Col 1
within the assigned range) like it is a mini worksheet within the standard
worksheet. Anyway see if the following example will point you in the right
direction. I am not sure that it is the best method but under test it works.

Sub EmptyColumn()

Dim rngOfData As Range
Dim lngColCount As Long
Dim lngRowCount As Long
Dim c As Long

With Sheets("Sheet1")
Set rngOfData = .Range("O8:Z18")
End With

lngColCount = rngOfData.Columns.Count
lngRowCount = rngOfData.Rows.Count

For c = 1 To lngColCount
If WorksheetFunction.CountA(rngOfData.Columns(c)) = 0 Then
MsgBox "First blank column is " & c
Exit For
End If
Next c

'Then to reference the cells in that column
'within rngOfData
With rngOfData
Range(.Cells(1, c), .Cells(lngRowCount, c)).Select
End With

End Sub



--
Regards,

OssieMac


"Kirsty" wrote:

Hi,
I have a table from cells O8:Z18 and I want a macro that will find the next
empty column in this area and paste data.

Everything I have tried, finds the next empty column that is outside this
area, does anyone know how I do this?

find next blank column in set area
 

Thanks, was exactly what was required

"OssieMac" wrote:

Hi Kirsty,

I have assumed that it is the first empty column within the range O8:Z18 and
not necessarily the entire column in the worksheet which is empty. Not sure
of your experience with VBA but just in case, when you assign a range to a
range variable then the cells actually start from Cells(1,1) (ie.Row 1, Col 1
within the assigned range) like it is a mini worksheet within the standard
worksheet. Anyway see if the following example will point you in the right
direction. I am not sure that it is the best method but under test it works.

Sub EmptyColumn()

Dim rngOfData As Range
Dim lngColCount As Long
Dim lngRowCount As Long
Dim c As Long

With Sheets("Sheet1")
Set rngOfData = .Range("O8:Z18")
End With

lngColCount = rngOfData.Columns.Count
lngRowCount = rngOfData.Rows.Count

For c = 1 To lngColCount
If WorksheetFunction.CountA(rngOfData.Columns(c)) = 0 Then
MsgBox "First blank column is " & c
Exit For
End If
Next c

'Then to reference the cells in that column
'within rngOfData
With rngOfData
Range(.Cells(1, c), .Cells(lngRowCount, c)).Select
End With

End Sub



--
Regards,

OssieMac


"Kirsty" wrote:

Hi,
I have a table from cells O8:Z18 and I want a macro that will find the next
empty column in this area and paste data.

Everything I have tried, finds the next empty column that is outside this
area, does anyone know how I do this?