How to get relative range?
 

I am struggling with these problems in my macros:

How to code:
1. Move one row down in the same column?
2. Move one row down and one column right (left)?
3. Fill down to the last non empty cell in the left (right) column.

Basically I want to write a macro which will find duplicates and mark them
in the next column. How to modify statements below so they are universal
(relative) rather than specific. Example, I have numbers in A2: A150 and I
want to find duplicates, which is easy to do with code below. Now I want to
change it so it writes the formula into the column where the cell is
selected and fill it down just to row 150.

If I want to find duplicates in column G then I would click H1 and the macro
would do the rest.

Sub Duplicates()
ActiveCell.FormulaR1C1 = "Duplicates"
Range("B2").Select '<============ This should be one cell down
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],1,"""")"
Range("B2:B5000").Select '<== This should from the last active down to
the last non empty cell in left column
Selection.FillDown
Range("B2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
Rows("2:5001").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("B2").Select
End Sub

Appreciate your help

Thanks
Jan

How to get relative range?
 

Hi Jan
for your questions:
sub foo()
Dim rng as range
Dim rng2

set rng = Activecell
'move one row down
rng.offset(1,0).value = something

'move one down and one to the right
rng.offset(1,1).value = something else

'select from active cell to last used row try

Set rng2 =
Range(cells(rng.row,rng.column),Cells(Rows.Count,r ng.column).End(xlUp).
Row)


--
Regards
Frank Kabel
Frankfurt, Germany

Jan Nademlejnsky wrote:
I am struggling with these problems in my macros:

How to code:
1. Move one row down in the same column?
2. Move one row down and one column right (left)?
3. Fill down to the last non empty cell in the left (right) column.

Basically I want to write a macro which will find duplicates and mark
them in the next column. How to modify statements below so they are
universal (relative) rather than specific. Example, I have numbers in
A2: A150 and I want to find duplicates, which is easy to do with code
below. Now I want to change it so it writes the formula into the
column where the cell is selected and fill it down just to row 150.

If I want to find duplicates in column G then I would click H1 and
the macro would do the rest.

Sub Duplicates()
ActiveCell.FormulaR1C1 = "Duplicates"
Range("B2").Select '<============ This should be one cell down
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],1,"""")"
Range("B2:B5000").Select '<== This should from the last active
down to the last non empty cell in left column
Selection.FillDown
Range("B2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
Rows("2:5001").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("B2").Select
End Sub

Appreciate your help

Thanks
Jan

How to get relative range?
 

Jan,

Struggled with your code, so I basically re-wrote it

Sub Duplicates()
With Range("A1")
.FormulaR1C1 = "Duplicates"
With .Offset(1, 1)
.FormulaR1C1 = "=RC[-1]=R[-1]C[-1]"
.AutoFill Destination:=.Resize(Cells(Rows.Count,
"A").End(xlUp).Row - 1), Type:=xlFillDefault
.AutoFilter Field:=2, Criteria1:="True"
Cells.SpecialCells(xlCellTypeVisible).EntireRow.De lete
.AutoFilter
End With
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jan Nademlejnsky" wrote in message
...
I am struggling with these problems in my macros:

How to code:
1. Move one row down in the same column?
2. Move one row down and one column right (left)?
3. Fill down to the last non empty cell in the left (right) column.

Basically I want to write a macro which will find duplicates and mark them
in the next column. How to modify statements below so they are universal
(relative) rather than specific. Example, I have numbers in A2: A150 and I
want to find duplicates, which is easy to do with code below. Now I want
to
change it so it writes the formula into the column where the cell is
selected and fill it down just to row 150.

If I want to find duplicates in column G then I would click H1 and the
macro
would do the rest.

Sub Duplicates()
ActiveCell.FormulaR1C1 = "Duplicates"
Range("B2").Select '<============ This should be one cell down
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],1,"""")"
Range("B2:B5000").Select '<== This should from the last active down
to
the last non empty cell in left column
Selection.FillDown
Range("B2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
Rows("2:5001").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("B2").Select
End Sub

Appreciate your help

Thanks
Jan

How to get relative range?
 

Hi Bob, this is exactly what I am looking for. Could you also solve the
filter problem, please. The filter refers to column 2 (Field 2) but it could
be any column.

Thanks

Jan


"Bob Phillips" wrote in message
...
Jan,

Struggled with your code, so I basically re-wrote it

Sub Duplicates()
With Range("A1")
.FormulaR1C1 = "Duplicates"
With .Offset(1, 1)
.FormulaR1C1 = "=RC[-1]=R[-1]C[-1]"
.AutoFill Destination:=.Resize(Cells(Rows.Count,
"A").End(xlUp).Row - 1), Type:=xlFillDefault
.AutoFilter Field:=2, Criteria1:="True"
Cells.SpecialCells(xlCellTypeVisible).EntireRow.De lete
.AutoFilter
End With
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jan Nademlejnsky" wrote in message
...
I am struggling with these problems in my macros:

How to code:
1. Move one row down in the same column?
2. Move one row down and one column right (left)?
3. Fill down to the last non empty cell in the left (right) column.

Basically I want to write a macro which will find duplicates and mark
them
in the next column. How to modify statements below so they are universal
(relative) rather than specific. Example, I have numbers in A2: A150 and
I
want to find duplicates, which is easy to do with code below. Now I want
to
change it so it writes the formula into the column where the cell is
selected and fill it down just to row 150.

If I want to find duplicates in column G then I would click H1 and the
macro
would do the rest.

Sub Duplicates()
ActiveCell.FormulaR1C1 = "Duplicates"
Range("B2").Select '<============ This should be one cell down
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],1,"""")"
Range("B2:B5000").Select '<== This should from the last active down
to
the last non empty cell in left column
Selection.FillDown
Range("B2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
Rows("2:5001").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("B2").Select
End Sub

Appreciate your help

Thanks
Jan

How to get relative range?
 

Jan,

Exactly what filter problem, as I thought I already had<g?

Can you explain some more?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jan Nademlejnsky" wrote in message
...
Hi Bob, this is exactly what I am looking for. Could you also solve the
filter problem, please. The filter refers to column 2 (Field 2) but it
could
be any column.

Thanks

Jan


"Bob Phillips" wrote in message
...
Jan,

Struggled with your code, so I basically re-wrote it

Sub Duplicates()
With Range("A1")
.FormulaR1C1 = "Duplicates"
With .Offset(1, 1)
.FormulaR1C1 = "=RC[-1]=R[-1]C[-1]"
.AutoFill Destination:=.Resize(Cells(Rows.Count,
"A").End(xlUp).Row - 1), Type:=xlFillDefault
.AutoFilter Field:=2, Criteria1:="True"
Cells.SpecialCells(xlCellTypeVisible).EntireRow.De lete
.AutoFilter
End With
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jan Nademlejnsky" wrote in message
...
I am struggling with these problems in my macros:

How to code:
1. Move one row down in the same column?
2. Move one row down and one column right (left)?
3. Fill down to the last non empty cell in the left (right) column.

Basically I want to write a macro which will find duplicates and mark
them
in the next column. How to modify statements below so they are
universal
(relative) rather than specific. Example, I have numbers in A2: A150
and
I
want to find duplicates, which is easy to do with code below. Now I
want
to
change it so it writes the formula into the column where the cell is
selected and fill it down just to row 150.

If I want to find duplicates in column G then I would click H1 and the
macro
would do the rest.

Sub Duplicates()
ActiveCell.FormulaR1C1 = "Duplicates"
Range("B2").Select '<============ This should be one cell down
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],1,"""")"
Range("B2:B5000").Select '<== This should from the last active
down
to
the last non empty cell in left column
Selection.FillDown
Range("B2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
Rows("2:5001").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("B2").Select
End Sub

Appreciate your help

Thanks
Jan

How to get relative range?
 

Jan,

Do you mean

Sub Duplicates()
With Activecell
.FormulaR1C1 = "Duplicates"
With .Offset(1, 1)
.FormulaR1C1 = "=RC[-1]=R[-1]C[-1]"
.AutoFill Destination:=.Resize(Cells(Rows.Count,
Activecell.Column).End(xlUp).Row - 1), Type:=xlFillDefault
.AutoFilter Field:=2, Criteria1:="True"
Cells.SpecialCells(xlCellTypeVisible).EntireRow.De lete
.AutoFilter
End With
End With
End Sub


In this, the data to be filtered should be activated, and then a formula and
filter is built in the next column. So activate say G1 where the data is and
run the macro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Jan,

Exactly what filter problem, as I thought I already had<g?

Can you explain some more?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jan Nademlejnsky" wrote in message
...
Hi Bob, this is exactly what I am looking for. Could you also solve the
filter problem, please. The filter refers to column 2 (Field 2) but it
could
be any column.

Thanks

Jan


"Bob Phillips" wrote in message
...
Jan,

Struggled with your code, so I basically re-wrote it

Sub Duplicates()
With Range("A1")
.FormulaR1C1 = "Duplicates"
With .Offset(1, 1)
.FormulaR1C1 = "=RC[-1]=R[-1]C[-1]"
.AutoFill Destination:=.Resize(Cells(Rows.Count,
"A").End(xlUp).Row - 1), Type:=xlFillDefault
.AutoFilter Field:=2, Criteria1:="True"
Cells.SpecialCells(xlCellTypeVisible).EntireRow.De lete
.AutoFilter
End With
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jan Nademlejnsky" wrote in message
...
I am struggling with these problems in my macros:

How to code:
1. Move one row down in the same column?
2. Move one row down and one column right (left)?
3. Fill down to the last non empty cell in the left (right) column.

Basically I want to write a macro which will find duplicates and
mark
them
in the next column. How to modify statements below so they are
universal
(relative) rather than specific. Example, I have numbers in A2: A150
and
I
want to find duplicates, which is easy to do with code below. Now I
want
to
change it so it writes the formula into the column where the cell is
selected and fill it down just to row 150.

If I want to find duplicates in column G then I would click H1 and
the
macro
would do the rest.

Sub Duplicates()
ActiveCell.FormulaR1C1 = "Duplicates"
Range("B2").Select '<============ This should be one cell down
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],1,"""")"
Range("B2:B5000").Select '<== This should from the last active
down
to
the last non empty cell in left column
Selection.FillDown
Range("B2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
Rows("2:5001").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("B2").Select
End Sub

Appreciate your help

Thanks
Jan