Help with deleting rows where the value of the first column isn't divisable by a certain number.

[Kathi]

Hi!

I'm working with a pretty big set of info. There are 3 columns and I
want to go through from top to bottom and entirely remove any rows
where the A column's value is not divisable by 15.

Here is the code I've tried to work with:

For I = 9 To 22919
If Me.Cells(I, 1) Mod 15 <> 0 Then
Me.Rows(I).Delete
End If
Next


I want check the values in ("A1:A22919")

When I run that code it just deletes every other row. And I'm not
quite sure what is wrong--I know a little about Visual Basic itself,
but I'm unfamiliar using it with Excel :\

I got this together by browsing through Google groups and search, but I
couldn't put it together from different parts and fooling around with
it for a few hours hasn't helped much either ;_;

[Rowan]

It works better going from bottom to top when deleteing rows. So try
something like this (save file before running)

Sub delRows()

Dim endRow As Long
Dim i As Long

endRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = endRow To 1 Step -1
If Cells(i, 1) Mod 15 <> 0 Then
Cells(i, 1).EntireRow.Delete
End If
Next i

End Sub

Hope this helps
Rowan

"Kathi" wrote:

> Hi!
>
> I'm working with a pretty big set of info. There are 3 columns and I
> want to go through from top to bottom and entirely remove any rows
> where the A column's value is not divisable by 15.
>
> Here is the code I've tried to work with:
>
> For I = 9 To 22919
> If Me.Cells(I, 1) Mod 15 <> 0 Then
> Me.Rows(I).Delete
> End If
> Next
>
>
> I want check the values in ("A1:A22919")
>
> When I run that code it just deletes every other row. And I'm not
> quite sure what is wrong--I know a little about Visual Basic itself,
> but I'm unfamiliar using it with Excel :\
>
> I got this together by browsing through Google groups and search, but I
> couldn't put it together from different parts and fooling around with
> it for a few hours hasn't helped much either ;_;
>
>

[Bob Phillips]

Be aware that the VBA MOD rounds numbers to integers, so you may not get
what you want. For instance, in VBA, if the cell contains 15.3, then
Cells(i,1) Mod 15 will return 0 and the row will not be deleted, 15.7 would
be deleted, which may not be what you want. If you don't want that, use

If Int(Cells(i,1).Value) = Cells(i,1).Value And _
Cells(i,1).value Mod 15 <> 0
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rowan" <Rowan@discussions.microsoft.com> wrote in message
news:7223E980-1CAB-4A30-9AAA-EA1BD3354E49@microsoft.com...
> It works better going from bottom to top when deleteing rows. So try
> something like this (save file before running)
>
> Sub delRows()
>
> Dim endRow As Long
> Dim i As Long
>
> endRow = Cells(Rows.Count, 1).End(xlUp).Row
>
> For i = endRow To 1 Step -1
> If Cells(i, 1) Mod 15 <> 0 Then
> Cells(i, 1).EntireRow.Delete
> End If
> Next i
>
> End Sub
>
> Hope this helps
> Rowan
>
> "Kathi" wrote:
>
> > Hi!
> >
> > I'm working with a pretty big set of info. There are 3 columns and I
> > want to go through from top to bottom and entirely remove any rows
> > where the A column's value is not divisable by 15.
> >
> > Here is the code I've tried to work with:
> >
> > For I = 9 To 22919
> > If Me.Cells(I, 1) Mod 15 <> 0 Then
> > Me.Rows(I).Delete
> > End If
> > Next
> >
> >
> > I want check the values in ("A1:A22919")
> >
> > When I run that code it just deletes every other row. And I'm not
> > quite sure what is wrong--I know a little about Visual Basic itself,
> > but I'm unfamiliar using it with Excel :\
> >
> > I got this together by browsing through Google groups and search, but I
> > couldn't put it together from different parts and fooling around with
> > it for a few hours hasn't helped much either ;_;
> >
> >

[Kathi]

Awesome!

I forgot that the integer types don't have decimels -_-;;

Its been a while since I did anything. I actually have only just
finished my first year in college, so I don't have much experience ^^;

Thanks alot you two <3