ExcelBanter - help with macro vba

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - help with macro vba (https://www.excelbanter.com/excel-programming/410881-help-macro-vba.html)

help with macro vba

I want to edit my macro (created by recording). Can you help me with the
codes for the following

I want to change this:
Selection.AutoFill Destination:=Range("C2:C1489")

to this:
Selection.AutoFill Destination:=Range("C2:"&Range(LastRow,
LastColumn).Address)
where the LastRow and LastColum, both integer, store the last row and last
column with data in a sheet.

I want to change this:
Range("A1:F1489").Sort
to this:
Range("A1:F" C2:"&Range(LastRow, LastColumn).Address)).Sort

I want to change this:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:R1489"
to:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:"&Range(LastRow, LastColumn).Address

Can you please correct the syntaxes above. I am trying to replace the actual
row and columns to an genarallized row and column number. The variables
LastRow and LastColumn stores the numeric values of last row and column

Pls help

Thanks
Mario

help with macro vba

See modified statements below.

Selection.AutoFill Destination:=Cells(LastRow, LastColumn).Address)

Range("A1:F" C2:"& Cells(LastRow, LastColumn).Address).Sort

ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:"& Cells(LastRow, LastColumn).Address

That should do it.

"mario" wrote:

I want to edit my macro (created by recording). Can you help me with the
codes for the following

I want to change this:
Selection.AutoFill Destination:=Range("C2:C1489")

to this:
Selection.AutoFill Destination:=Range("C2:"&Range(LastRow,
LastColumn).Address)
where the LastRow and LastColum, both integer, store the last row and last
column with data in a sheet.

I want to change this:
Range("A1:F1489").Sort
to this:
Range("A1:F" C2:"&Range(LastRow, LastColumn).Address)).Sort

I want to change this:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:R1489"
to:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:"&Range(LastRow, LastColumn).Address

Can you please correct the syntaxes above. I am trying to replace the actual
row and columns to an genarallized row and column number. The variables
LastRow and LastColumn stores the numeric values of last row and column

Pls help

Thanks
Mario

help with macro vba

Hi

See comments below.

I want to edit my macro (created by recording). Can you help me with the
codes for the following

I want to change this:
Selection.AutoFill Destination:=Range("C2:C1489")

to this:
Selection.AutoFill Destination:=Range("C2:"&Range(LastRow,
LastColumn).Address)
where the LastRow and LastColum, both integer, store the last row and last
column with data in a sheet.

Selection.AutoFill Destination:=Range("C2", cells(LastRow,LastColumn))

I want to change this:
Range("A1:F1489").Sort
to this:
Range("A1:F" C2:"&Range(LastRow, LastColumn).Address)).Sort

Range("C2", cells(LastRow,LastColumn)).Sort

I'm not sure if the first cell should be C2 or A1. Change to suit!

I want to change this:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:R1489"
to:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:"&Range(LastRow, LastColumn).Address

Dim LastCell as string
LastCell=Cells(LastRow,LastColumn).Address
ActiveWorkbook.Names.Add Name:="MyRangeName", _
RefersTo:="Sheet1!B2:" & LastCell

Can you please correct the syntaxes above. I am trying to replace the
actual
row and columns to an genarallized row and column number. The variables
LastRow and LastColumn stores the numeric values of last row and column

Pls help

Thanks
Mario

Best regards,
Per

help with macro vba

Add these Functions from Ron de Bruin to a module to retrieve and store the
LastRow and LastCol integers.

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Gord Dibben MS Excel MVP
On Tue, 13 May 2008 12:17:01 -0700, mario
wrote:

I want to edit my macro (created by recording). Can you help me with the
codes for the following

I want to change this:
Selection.AutoFill Destination:=Range("C2:C1489")

to this:
Selection.AutoFill Destination:=Range("C2:"&Range(LastRow,
LastColumn).Address)
where the LastRow and LastColum, both integer, store the last row and last
column with data in a sheet.

I want to change this:
Range("A1:F1489").Sort
to this:
Range("A1:F" C2:"&Range(LastRow, LastColumn).Address)).Sort

I want to change this:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:R1489"
to:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:"&Range(LastRow, LastColumn).Address

Can you please correct the syntaxes above. I am trying to replace the actual
row and columns to an genarallized row and column number. The variables
LastRow and LastColumn stores the numeric values of last row and column

Pls help

Thanks
Mario

help with macro vba

Thanks it worked fine.

"Gord Dibben" wrote:

Add these Functions from Ron de Bruin to a module to retrieve and store the
LastRow and LastCol integers.

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Gord Dibben MS Excel MVP
On Tue, 13 May 2008 12:17:01 -0700, mario
wrote:

I want to edit my macro (created by recording). Can you help me with the
codes for the following

I want to change this:
Selection.AutoFill Destination:=Range("C2:C1489")

to this:
Selection.AutoFill Destination:=Range("C2:"&Range(LastRow,
LastColumn).Address)
where the LastRow and LastColum, both integer, store the last row and last
column with data in a sheet.

I want to change this:
Range("A1:F1489").Sort
to this:
Range("A1:F" C2:"&Range(LastRow, LastColumn).Address)).Sort

I want to change this:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:R1489"
to:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersToR1C1:= _
"=Sheet1!R2C2:"&Range(LastRow, LastColumn).Address

Can you please correct the syntaxes above. I am trying to replace the actual
row and columns to an genarallized row and column number. The variables
LastRow and LastColumn stores the numeric values of last row and column

Pls help

Thanks
Mario