Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with deleting rows where the value of the first column isn't divisable by a certain number.
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 ;_; |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with deleting rows where the value of the first column isn't
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 ;_; |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with deleting rows where the value of the first column isn't
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" wrote in message ... 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 ;_; |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with deleting rows where the value of the first column isn't divisable by a certain number.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting number and alphabet columns and rows. | Excel Discussion (Misc queries) | |||
Deleting rows based on number of characters | Excel Discussion (Misc queries) | |||
Deleting 3 rows when current row number is unknown | Excel Programming | |||
Deleting rows with repeated number | Excel Programming | |||
deleting variable number of rows | Excel Programming |