Find next column in sheet

[Jenny B.]

Hello,

I'm looking for a way to locate the next empty column top (not the next cell
in a certain row, but next possible column starting in row 1). I will be
pasting data from another page into this, but I need for my Macro to first
locate that particular cell. The name of the sheet I'm looking for the column
in is "Plant Salaried 2007".

Currently, that sheet has many filled columns of data (all the same length
starting a row 1 and ending at row 25). I've tried to modify other codes
noted on this discussion site, but some I cannot follow and others only aim
to locate a cell in one particular row.

Thank you for any thoughts with this one - Jenny B.

 

[macropod]

Hi Jenny,

Depending on whether the entire column needs to be empty, or just the first row, you could use one of the following two approaches
to locate & use the first such column - even if there are subsequent used columns:

Sub Test1()
Dim i As Integer
With ThisWorkbook.Sheets("Plant Salaried 2007")
For i = 1 To .UsedRange.Columns.Count + 1
If Application.WorksheetFunction.CountA(Columns(i)) = 0 Then
'Do whatever you want. eg:
.Cells(1, i).Select
Exit Sub
End If
Next
End With
End Sub

Sub Test2()
Dim i As Integer
With ThisWorkbook.Sheets("Plant Salaried 2007")
For i = 1 To .UsedRange.Columns.Count + 1
If .Cells(1, i).Value = 0 Then
'Do whatever you want. eg:
.Cells(1, i).Select
Exit Sub
End If
Next
End With
End Sub

If you just want to use the last + 1 column:

Sub Test3()
With ThisWorkbook.Sheets("Plant Salaried 2007")
'Do whatever you want. eg:
.Cells(1, .UsedRange.Columns.Count + 1).Select
End With
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


| Hello,
|
| I'm looking for a way to locate the next empty column top (not the next cell
| in a certain row, but next possible column starting in row 1). I will be
| pasting data from another page into this, but I need for my Macro to first
| locate that particular cell. The name of the sheet I'm looking for the column
| in is "Plant Salaried 2007".
|
| Currently, that sheet has many filled columns of data (all the same length
| starting a row 1 and ending at row 25). I've tried to modify other codes
| noted on this discussion site, but some I cannot follow and others only aim
| to locate a cell in one particular row.
|
| Thank you for any thoughts with this one - Jenny B.
|

 

[Ken Johnson]

Hello,

I'm looking for a way to locate the next empty column top (not the next cell
in a certain row, but next possible column starting in row 1). I will be
pasting data from another page into this, but I need for my Macro to first
locate that particular cell. The name of the sheet I'm looking for the column
in is "Plant Salaried 2007".

Currently, that sheet has many filled columns of data (all the same length
starting a row 1 and ending at row 25). I've tried to modify other codes
noted on this discussion site, but some I cannot follow and others only aim
to locate a cell in one particular row.

Thank you for any thoughts with this one - Jenny B.

Sheet3.Range("A1:A25").Copy _
Worksheets("Plant Salaried 2007").Cells(1,
Columns.Count).End(xlToLeft).Offset(0, 1)

copies A1:A25 on Sheet3 and pastes it into Plant Salaried 2007
starting in row 1 of the next available column.

Ken Johnson

 

[Gord Dibben]

Jenny

Sub Select_Last_Col()
ActiveSheet.Cells(1, ActiveSheet.Cells(1, Columns. _
Count).End(xlToLeft).Column).Select
End Sub


Gord Dibben MS Excel MVP

 

[Jenny B.]

Works like a charm.

Thank you all for your help once again - Jenny B.