How to get relative range?

  • Thread starter Thread starter Jan Nademlejnsky
  • Start date Start date
J

Jan Nademlejnsky

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
 
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,rng.column).End(xlUp).
Row)
 
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.Delete
.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)
 
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 said:
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.Delete
.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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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.Delete
.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 said:
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
Click to expand...
Click to expand...
and
I
want to find duplicates, which is easy to do with code below. Now I
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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.Delete
.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 said:
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 said:
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 said:
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.Delete
.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)

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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top