row (i:j).select
 

Hello,

I have this piece of code: I would like to input in row.select the
range of the row.

cell = Range("A" + CStr(i)).Value
cell2 = Range("A" + CStr(j)).Value

If (cell) = (cell2) And (cell) < " " Then


Rows("""i + ":" + j""").Select
Selection.Delete Shift:=xlUp

Else

i = i + 1
j = j + 1


End If

I have a problem in rows statement.

Thank you

Ina

row (i:j).select
 

Rows("i"&":"&"j").Select


HTH
--
AP

"ina" a écrit dans le message de
ups.com...
Hello,

I have this piece of code: I would like to input in row.select the
range of the row.

cell = Range("A" + CStr(i)).Value
cell2 = Range("A" + CStr(j)).Value

If (cell) = (cell2) And (cell) < " " Then


Rows("""i + ":" + j""").Select
Selection.Delete Shift:=xlUp

Else

i = i + 1
j = j + 1


End If

I have a problem in rows statement.

Thank you

Ina

row (i:j).select
 

Hi Ina,

instead of:

Rows("""i + ":" + j""").Select
Selection.Delete Shift:=xlUp

try this

range(cell.address & ":" & cell2.address).rows.delete shift:=xlup

In case I understood correctly what rows you want to delete, otherwise
fix the range.

Ivan

row (i:j).select
 

try this idea

Sub selectrng()
'Rows("3:6").Select
i = 3
j = 6
Rows(i & ":" & j).Delete Shift:=xlUp
'or
'Range(Cells(i, 1), Cells(j, 1)).EntireRow.Delete Shift:=xlUp
End Sub

--
Don Guillett
SalesAid Software

"ina" wrote in message
ups.com...
Hello,

I have this piece of code: I would like to input in row.select the
range of the row.

cell = Range("A" + CStr(i)).Value
cell2 = Range("A" + CStr(j)).Value

If (cell) = (cell2) And (cell) < " " Then


Rows("""i + ":" + j""").Select
Selection.Delete Shift:=xlUp

Else

i = i + 1
j = j + 1


End If

I have a problem in rows statement.

Thank you

Ina

row (i:j).select
 

Rows(i & ":" & j).Select

Usually when deleting rows, it is best to loop from the bottom up.

as you can see, if you deleted i = 2 and j = 3, then you add 1 to each, you
are now at row 3 and 4, but these are really the old 5 and 6 (the old 4 gets
skipped)

If you are deleting duplicate rows in a sorted list

last row = cells(rows.count,1).End(xlup)
for i = lastrow to 1 step -1
if cells(i,1).Value = cells(i-1,1).Value then
rows(i).Delete
end if
Next

You are deleting both rows, so I am not sure exactly what your doing, but
maybe the above will provide some ideas.

--
Regards,
Tom Ogilvy


"ina" wrote:

Hello,

I have this piece of code: I would like to input in row.select the
range of the row.

cell = Range("A" + CStr(i)).Value
cell2 = Range("A" + CStr(j)).Value

If (cell) = (cell2) And (cell) < " " Then


Rows("""i + ":" + j""").Select
Selection.Delete Shift:=xlUp

Else

i = i + 1
j = j + 1


End If

I have a problem in rows statement.

Thank you

Ina

row (i:j).select
 

Think you got confused. That would produce an error. for verfication: In the
immediate window

i = 10
j = 10
? "i"&":"&"j"
i:j
? rows("i"&":"&"j").address
' produced an error
--
Regards,
Tom Ogilvy


"Ardus Petus" wrote:

Rows("i"&":"&"j").Select


HTH
--
AP

"ina" a écrit dans le message de
ups.com...
Hello,

I have this piece of code: I would like to input in row.select the
range of the row.

cell = Range("A" + CStr(i)).Value
cell2 = Range("A" + CStr(j)).Value

If (cell) = (cell2) And (cell) < " " Then


Rows("""i + ":" + j""").Select
Selection.Delete Shift:=xlUp

Else

i = i + 1
j = j + 1


End If

I have a problem in rows statement.

Thank you

Ina

row (i:j).select
 

If he wants to delete the entire row, that wouldn't do it:

Verfied from the immediate window:
set cell = cells(3,1)
set cell2 = cells(8,1)
? range(cell.address & ":" & cell2.address).rows.address
$A$3:$A$8



perhaps you were looking for

range(cell.address & ":" & cell2.address).EntireRow.Delete

although
Range(cell,cell2).EntireRow.Delete

should be more efficient

--
Regards,
Tom Ogilvy


" wrote:

Hi Ina,

instead of:

Rows("""i + ":" + j""").Select
Selection.Delete Shift:=xlUp

try this

range(cell.address & ":" & cell2.address).rows.delete shift:=xlup

In case I understood correctly what rows you want to delete, otherwise
fix the range.

Ivan

row (i:j).select
 

Thanks a lot for this answers, effectively I would like to delete a
duplicate entry in a column.

Ina

row (i:j).select
 

lastrow should not have had a space and should only loop up to row 2:

lastrow = cells(rows.count,1).End(xlup)
for i = lastrow to 2 step -1
if cells(i,1).Value = cells(i-1,1).Value then
rows(i).Delete
end if
Next

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Rows(i & ":" & j).Select

Usually when deleting rows, it is best to loop from the bottom up.

as you can see, if you deleted i = 2 and j = 3, then you add 1 to each, you
are now at row 3 and 4, but these are really the old 5 and 6 (the old 4 gets
skipped)

If you are deleting duplicate rows in a sorted list

last row = cells(rows.count,1).End(xlup)
for i = lastrow to 1 step -1
if cells(i,1).Value = cells(i-1,1).Value then
rows(i).Delete
end if
Next

You are deleting both rows, so I am not sure exactly what your doing, but
maybe the above will provide some ideas.

--
Regards,
Tom Ogilvy


"ina" wrote:

Hello,

I have this piece of code: I would like to input in row.select the
range of the row.

cell = Range("A" + CStr(i)).Value
cell2 = Range("A" + CStr(j)).Value

If (cell) = (cell2) And (cell) < " " Then


Rows("""i + ":" + j""").Select
Selection.Delete Shift:=xlUp

Else

i = i + 1
j = j + 1


End If

I have a problem in rows statement.

Thank you

Ina

row (i:j).select
 

Sub deleteDupsInSortedList()
Dim icol as Long, lastrow as Long

icol = 1
lastrow = cells(rows.count,icol).End(xlup)
for i = lastrow to 2 step -1
if cells(i,1).Value = cells(i-1,icol).Value then
rows(i).Delete
end if
Next i

End Sub

--
Regards,
Tom Ogilvy


"ina" wrote:

Thanks a lot for this answers, effectively I would like to delete a
duplicate entry in a column.

Ina