Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find blank cell in column | New Users to Excel | |||
Find next blank column with Macro | Excel Programming | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Find 1st blank cell in column & sum to the same row in another col | Excel Worksheet Functions | |||
Find first non-blank or non-zero in a column of data | Excel Discussion (Misc queries) |