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?
|