Using VBA macro to locate next free column on worksheet

I need a macro that locates the next free column (i.e. has no data in cells) and then places the cursor in a cell on a designated row. Would like search to start from column B and to place the cursor on row 4 on the free column.
<>
My VBA code thus far:

Function NewColNumber(Range1 As Range) As Integer

Dim j As Integer

For j = 1 To Range1.Columns.Count

If Range1.Columns(j) = "" Then

NewColNumber = Range1.Columns(j).Column

Exit Function

End If

End Function

Sub SaveCOLComments()

On Error GoTo Err_Part

Dim Range1 As Range

Dim intNewColNumber As Integer

' Go to Sheet1 and COPY field range content the go to Sheet2

Sheets("Sheet1").Select

Range("F2:F6").Select

Selection.Copy

Sheets("Sheet2").Select

' Obtain the next free column using range set in range1

Set Range1 = Worksheets("Sheet2").Range("c4:z4")

' Obtain the next available column number and place cursor in row 4

intNewColNumber = NewColNumber(Range1)

Worksheets("Sheet2").Cells(intNewColNumber, 3).Select

' PASTE the field range content obtained from Sheet1 into row 4 of column

ActiveSheet.Paste

MsgBox ("The field content has been copied."), vbOKOnly, " Test Macro", a, a

Exit_Point:

Exit Sub

Err_Part:

MsgBox Error$()

Resume Exit_Point

End Sub

Answer this question

Using VBA macro to locate next free column on worksheet

Redgum
It's very useful. LastCol and its sister sub LastRow are in my code library and are often pulled into my projects.
```
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
```
Dinesh Patel
Try this to validate a column is empty or not. I know that I can make this more compact, but I can kind of too lazy, hehe. The Find function is basically a general driver for the same Find function in VBA, but take cares of error massage, and return boolean of found or not found. The Find_inOneColumn is self explaintory, general for all value. And the ColumnIsEmpty is the special case you want. You may want to think about treating a cell contains " " (spaces) is empty or not.

Function ColumnIsEmpty() As Boolean
ColumnIsEmpty= Not Find_inOneColumn("*")
End Function

Function Find_inOneColumn(vWhat As String, Optional vAfter As Range, _
Optional vLookInFormulas As Boolean = True, Optional vByPart As Boolean = True, _
Optional vMatchCase As Boolean = False, Optional vSearchFormat As Boolean = False) As Boolean

If IsNull(vAfter) Then Set vAfter = Range("A1")
Dim CurrentCol As Long
CurrentCol = vAfter.Column
If Find(vWhat, vAfter, vLookInFormulas, vByPart, False, vMatchCase, vSearchFormat) Then
If CurrentCol = ActiveCell.Column Then
Find_inOneColumn = True
Else
Find_inOneColumn = False
End If
Else
Find_inOneColumn = False
End If
End Function

Function Find(vWhat As String, Optional vAfter As Range, _
Optional vLookInFormulas As Boolean = True, _
Optional vByPart As Boolean = True, Optional vByRows As Boolean = True, _
Optional vMatchCase As Boolean = False, Optional vSearchFormat As Boolean = False) As Boolean

If IsNull(vAfter) Then Set vAfter = Range("A1")
vAfter.Select
Dim vLookInFormulas2, vByPart2, vByRows2
If vLookInFormulas Then
vLookInFormulas2 = xlFormulas
Else
vLookInFormulas2 = xlValues
End If

If vByPart Then
vByPart2 = xlPart
Else
vByPart2 = xlWhole
End If

If vByRows Then
vByRows2 = xlByRows
Else
vByRows2 = xlByColumns
End If

On Error GoTo FindByRow_Error_Handle
Cells.Find(What:=vWhat, After:=ActiveCell, LookIn:=vLookInFormulas2, _
LookAt:=vByPart2, SearchOrder:=vByRows2, SearchDirection:=xlNext, _
MatchCase:=vMatchCase).Activate
'MatchCase:=vMatchCase, SearchFormat:=vSearchFormat).Activate

Find = True
Exit Function
FindByRow_Error_Handle:
Find = False
End Function
eldiener
Woot, that's so much simplier than mine. Is this faster than using Find backward from the end of the sheet What's the performance of Find function

c-sharper2005

Another alternative is this function:

Function LastCol(rTest As Range) As Long
 Dim lTest As Long
 Dim iRow As Range
 For Each iRow In rTest.Rows
  With rTest.Parent.Cells(iRow.Row, "IV")
   lTest = IIf(.End(xlToLeft).Column > lTest, .End(xlToLeft).Column, lTest)
  End With
 Next
 LastCol = lTest
End Function

It returns the number of the last column with data in it. You call it like this:

MyLastCol = LastCol(ActiveSheet.UsedRange)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

T.Beal
You need to tell the code where to paste:

MyLastCol = LastCol(ActiveSheet.UsedRange)
ActiveSheet.Cells(1, MyLastCol + 1).Select
ActiveSheet.Paste
```
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
```
Carl Janssen
Just curious, what's a MVP Thanks :)

And I just realized that when I use Activesheet.UsedRange.Select, I select all of the data. If that's the case, wouldn't the last selected column be the LastCol How do I get the last column from there directly to save those loops The function is great for everything else. But seeing Activesheet.UsedRange already returns the last column info for the whole sheet, I hope I can skip a function to save more time.

Thanks.
xyte
Wow, nice.

Heheh, I edited my last post, but you are faster, so I repost it here.

I just realized that when I use Activesheet.UsedRange.Select, I select all of the data. If that's the case, wouldn't the last selected column be the LastCol How do I get the last column from there directly to save those loops The function is great for everything else. But seeing Activesheet.UsedRange already returns the last column info for the whole sheet, I hope I can skip a function to save more time.

Thanks.

Hehe, I am such an annoying guy. I just modified your code. O put variable column to support different excel sheet limit. And a boundary test to make sure "IV" column is really empty.

Function LastCol(rTest As Range) As Long
Dim lTest As Long
Dim iRow As Range
For Each iRow In rTest.Rows
    With rTest.Parent.Cells(iRow.Row, Columns.Count)
        If .Value <> "" Then
            lTest = IIf(.End(xlToLeft).Column > lTest, .End(xlToLeft).Column, lTest)
        Else
            lTest = .Column
        End If
    End With
Next
LastCol = lTest
End Function
DavidThi808
I do appreciate your posting but I cannot get it to work. It just completes the copy action to the active cell. Can you check belew and advise where I am going wrong please.

Function LastCol(rTest As Range) As Long
Dim lTest As Long
Dim iRow As Range
For Each iRow In rTest.Rows
With rTest.Parent.Cells(iRow.Row, Columns.Count)
If .Value <> "" Then
lTest = IIf(.End(xlToLeft).Column > lTest, .End(xlToLeft).Column, lTest)
Else
lTest = .Column
End If
End With
Next
LastCol = lTest
End Function

Sub SaveCOLComments()
On Error GoTo Err_Part
Dim Range1 As Range
Dim MyLastCol As Integer

' Go to Sheet1 and COPY field range content then go to Sheet2
Sheets("Sheet1").Select
Range("F2:F6").Select
Selection.Copy
Sheets("Sheet2").Select

' Obtain the Code column in range1 to LOCATE the next available column
Set Range1 = Worksheets("Sheet2").Range("b4:z4")

' Obtain the next available column number
MyLastCol = LastCol(ActiveSheet.UsedRange)

' PASTE the field range content obtained from Sheet1 into the next available column
ActiveSheet.Paste
MsgBox ("The field content has been copied."), vbOKOnly, " Test Macro", a, a

Exit_Point:
Exit Sub

Err_Part:
MsgBox Error$()
Resume Exit_Point

End Sub
RubenPieters
I don't know about the relative performance of my routine vs. Find. Mine is good for finding the last column in a particular row or set of rows, regardless of other rows which may have more columns filled. For example, if the header row has some extra cells with dummy data, and I want to know how large the range of data is, I will pass the range A2:A100 (ignoring A1) to my function.
```
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
```

Deco

What's an MVP:

http://support.microsoft.com/support/mvp/program.asp

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

faina
I use UsedRange frequently to copy data from a sheet. No need to select first.
```
With ActiveSheet.UsedRange
  LastCol = .Column + Columns.Count - 1
End With
```
Sometimes UsedRange doesn't count filled cells, but dirty cells, meaning they're empty but they were previously filled, or they contain formatting but not values. So I don't use it for precision work.

Good idea checking for an empty last column. I rarely use more than a few columns, so I didn't encounter that problem.
```
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
```
Hockey Nut
Oh yeah, I tested it out. It selects a formated cell. It seems like if the cell contains any information (visible or invisible), it is consider used. Thank you for pointing that out, and the new code, because I still can use it for my current region and other stuff. My current approach is to parse the address string, but I know it is not a proper way doing it.

I don't use that much of columns too, but I still take that into consideration. Hehe, that's why sometimes I think I am picky and annoying.

I learned so much here. The IFF, parent property, usedrange property, its special characteristics, and getting the range bound.

Thank you
KDS 1000
BTW, there is a stupid mistake on my, If .Value <> "" Then. Please change <> to =. Hehe.

If .Value <> "" Then
lTest = IIf(.End(xlToLeft).Column > lTest, .End(xlToLeft).Column, lTest)
Else
lTest = .Column
End If
gpugelni
Hummm... so true so true. Mine only find the last column in the whole sheet, not your custom defined range. Better suscribe to this thread before I upgrade my functions to this. This can be used for so many different situations, like a single row, a set of rows, and the whole sheet.

Thank you so much for your code.
Using VBA macro to locate next free column on worksheet

Answer this question