Deleting Rows

[[email protected]]

Hi

I am trying to delete a specific row, currently I am using

Rows(sheetcell(i)).Delete

which gets the number in the array sheetcell in this case 8. But
nothing actually happens, im guessing the syntax is wrong.

Any ideas?

Ta

[MattShoreson]

try cells(sheetcell(i),1).entirerow.delete

[Norman Jones]

Hi Lawrence,

If sheetcell(i) returns a value of 8, then your code deletes row 8 on the
current sheet for me.

--
---
Regards,
Norman



<[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I am trying to delete a specific row, currently I am using
>
> Rows(sheetcell(i)).Delete
>
> which gets the number in the array sheetcell in this case 8. But
> nothing actually happens, im guessing the syntax is wrong.
>
> Any ideas?
>
> Ta
>

[[email protected]]

I thought that as well, but nothing happens. The contents of the array
is definately 8 as an integer (different numbers, nothing happens
either).

And yes the code does execute as I have used break points etc but no
errors and no deleting actually occurs...grrrr

This is a bigger section of the code if it helps:

If ((Selection.Value <> "") And (sheetmemo(i) = "")) Then
Rows(sheetcell(i)).Delete
sheetmemo(i) = Selection.Value
ActiveCell.Offset(0, -5).Select
sheetarray(i) = Selection.Value
sheetcell(i) = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
ignore = 1
Exit For
End If

Thanks

[[email protected]]

Just tried that Matt but still doesnt work

[MattShoreson]

is your code hitting this line when you debug?

'Rows(sheetcell(i)).Delete'

It may be your if statement isnt evaluating.

[Norman Jones]

Hi Lawrence,

If the row of interest is not deleted, it is probable that your condition:

> If ((Selection.Value <> "") And (sheetmemo(i) = "")) Then

is not being satisfied.

Try adding a break point and test the condition's value.


---
Regards,
Norman



<[email protected]> wrote in message
news:[email protected]...
>I thought that as well, but nothing happens. The contents of the array
> is definately 8 as an integer (different numbers, nothing happens
> either).
>
> And yes the code does execute as I have used break points etc but no
> errors and no deleting actually occurs...grrrr
>
> This is a bigger section of the code if it helps:
>
> If ((Selection.Value <> "") And (sheetmemo(i) = "")) Then
> Rows(sheetcell(i)).Delete
> sheetmemo(i) = Selection.Value
> ActiveCell.Offset(0, -5).Select
> sheetarray(i) = Selection.Value
> sheetcell(i) = ActiveCell.Row
> ActiveCell.Offset(1, 0).Select
> ignore = 1
> Exit For
> End If
>
> Thanks
>

[[email protected]]

Yes it is hitting that line. Ive put break points all around it and it
definately executes.

This is the whole of my code although I dont think it will help:

Private Sub CommandButton1_Click()
Dim sheetarray(5000) As String
Dim sheetmemo(5000) As String
Dim sheetcell(5000) As Integer
Dim arraynum As Integer
Dim ignore As Integer
Dim temp As String
arraynum = 0
Sheet3.Activate
Sheet3.Range("A4").Select

Sheet2.Activate
Sheet2.Range("D2").Select

Do While Selection.Value <> ""
ignore = 0
For i = 0 To arraynum
If Selection.Value = sheetarray(i) Then
Selection.Copy
Sheet3.Activate
Paste
ActiveCell.Offset(1, 0).Select
Sheet2.Activate

ActiveCell.Offset(0, 5).Select
If Selection.Value <> sheetmemo(i) Then

If ((Selection.Value <> "") And (sheetmemo(i) <>
"")) Then
ActiveCell.Offset(1, -5).Select
Exit For
End If
If ((Selection.Value = "") And (sheetmemo(i) <>
"")) Then
ActiveCell.Offset(0, -5).Select
Rows(ActiveCell.Row).Delete

ignore = 1
Exit For
End If

If ((Selection.Value <> "") And (sheetmemo(i) =
"")) Then
Cells(sheetcell(i), 1).EntireRow.Delete
sheetmemo(i) = Selection.Value
ActiveCell.Offset(0, -5).Select
sheetarray(i) = Selection.Value
sheetcell(i) = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
ignore = 1
Exit For
End If

End If
ActiveCell.Offset(0, -5).Select

End If
Next
If ignore = 0 Then
sheetarray(arraynum) = Selection.Value
ActiveCell.Offset(0, 5).Select
sheetmemo(arraynum) = Selection.Value
ActiveCell.Offset(0, -5).Select
sheetcell(arraynum) = ActiveCell.Row
arraynum = arraynum + 1
ActiveCell.Offset(1, 0).Select
End If
Loop


End Sub

Its quite odd why it doesnt work. As you can see activecell.row gets
put into the sheetcell array which is where the row number i want to
delete comes from.

Thanks


MattShoreson wrote:

> is your code hitting this line when you debug?
>
> 'Rows(sheetcell(i)).Delete'
>
> It may be your if statement isnt evaluating.
>
>
> --
> MattShoreson
> ------------------------------------------------------------------------
> MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
> View this thread: http://www.excelforum.com/showthread...hreadid=563642

[MattShoreson]

you need to qualify your statement...

Sheet2.Cells(sheetcell(i), 1).EntireRow.Delete