Hi all,

I was wondering what the syntax would be for last column.
Like I have for lastRow:

lastRow = .Range("B" & Rows.count).End(xlUp).Row

I need something similar for looking an first empty col. to the right.
Pleae help.
Thank you
Sye

--
sazi
-----------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...nfo&userid=635
View this thread: http://www.excelforum.com/showthread.php?threadid=49467

For row 1 - change 1 as required:

Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column + 1

"saziz" wrote:


Hi all,

I was wondering what the syntax would be for last column.
Like I have for lastRow:

lastRow = .Range("B" & Rows.count).End(xlUp).Row

I need something similar for looking an first empty col. to the right.
Pleae help.
Thank you
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=494676

lastColumn = .Cells(1, columns.count).End(xltoright).column


--
HTH...

Jim Thomlinson


"saziz" wrote:


Hi all,

I was wondering what the syntax would be for last column.
Like I have for lastRow:

lastRow = .Range("B" & Rows.count).End(xlUp).Row

I need something similar for looking an first empty col. to the right.
Pleae help.
Thank you
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=494676

Sorry... The other Right...

lastColumn = .Cells(1, columns.count).End(xltoleft).column
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

lastColumn = .Cells(1, columns.count).End(xltoright).column


--
HTH...

Jim Thomlinson


"saziz" wrote:


Hi all,

I was wondering what the syntax would be for last column.
Like I have for lastRow:

lastRow = .Range("B" & Rows.count).End(xlUp).Row

I need something similar for looking an first empty col. to the right.
Pleae help.
Thank you
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=494676

If there are no blanks

lastCol = .Range("A1").End(xlToRight).Column

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"saziz" wrote in
message ...

Hi all,

I was wondering what the syntax would be for last column.
Like I have for lastRow:

lastRow = .Range("B" & Rows.count).End(xlUp).Row

I need something similar for looking an first empty col. to the right.
Pleae help.
Thank you
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=494676

Hi Jim & bob,
I used both of your code, it is pasting over the same columns. I a
asking it to find the next empty col. and paste.

Here is my code:

Sub mycode12()

Dim c As Range
Dim rRng As Range
'Dim lastRow As Long
Dim count As Long
Dim lastcol As Long


'Formula Worksheets(1).Range("a2") = Mid(CELL("filename", A1)
Find("]", CELL("filename", A1)) + 1, 255)
With Worksheets(1).Range("A2").Select
ActiveCell.FormulaR1C1 = _

"=MID(CELL(""filename"",R[-3]C[-6]),FIND(""["",CELL(""filename"",R[-3]C[-6]))+1,FIND(""]"",CELL(""filename"",R[-3]C[-6]))-FIND(""["",CELL(""filename"",R[-3]C[-6]))-1)"

With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
.Range("A1:E" & c.Row - 1).Copy
End If
End With

Windows("DataAll.xls").Activate
With Worksheets("Sheet2")

lastcol = .Range("A1").End(xlToRight).Column
.Cells(lastColumn + 1, 1).PasteSpecial Paste:=xlValues _
, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
count = 5
Do

If Application.Range("B" & count) = "Orifice Axis 1" Then
Application.Range("B" & count).Select
Selection.EntireRow.Delete
End If
count = count + 1
Loop Until Application.Range("B" & count).Value = ""
End With
End With
End Sub

Thanks
Sye

--
sazi
-----------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...nfo&userid=635
View this thread: http://www.excelforum.com/showthread.php?threadid=49467

You are using the column id in the row argument

Cells(1,lastColumn + 1).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"saziz" wrote in
message ...

Hi Jim & bob,
I used both of your code, it is pasting over the same columns. I am
asking it to find the next empty col. and paste.

Here is my code:

Sub mycode12()

Dim c As Range
Dim rRng As Range
'Dim lastRow As Long
Dim count As Long
Dim lastcol As Long


'Formula Worksheets(1).Range("a2") = Mid(CELL("filename", A1),
Find("]", CELL("filename", A1)) + 1, 255)
With Worksheets(1).Range("A2").Select
ActiveCell.FormulaR1C1 = _


"=MID(CELL(""filename"",R[-3]C[-6]),FIND(""["",CELL(""filename"",R[-3]C[-6])
)+1,FIND(""]"",CELL(""filename"",R[-3]C[-6]))-FIND(""["",CELL(""filename"",R
[-3]C[-6]))-1)"

With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
Range("A1:E" & c.Row - 1).Copy
End If
End With

Windows("DataAll.xls").Activate
With Worksheets("Sheet2")

lastcol = .Range("A1").End(xlToRight).Column
Cells(lastColumn + 1, 1).PasteSpecial Paste:=xlValues _
, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
count = 5
Do

If Application.Range("B" & count) = "Orifice Axis 1" Then
Application.Range("B" & count).Select
Selection.EntireRow.Delete
End If
count = count + 1
Loop Until Application.Range("B" & count).Value = ""
End With
End With
End Sub

Thanks
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=494676

Hi bob,
What is the correct method for col argument?
Thanks
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=494676

As I showed you, the format of Cells is

Cells(row_num,column)

and can take the form

Cells(1,1)
or
Cells(1,"A")

but the column is the second argument.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"saziz" wrote in
message ...

Hi bob,
What is the correct method for col argument?
Thanks
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=494676