last column..?

[saziz]

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

 

[Guest]

For row 1 - change 1 as required:

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

 

[Guest]

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

 

[Guest]

Sorry... The other Right...

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

 

[Bob Phillips]

If there are no blanks

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[saziz]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)