excel - Delete row if 2 conditions are met - change in time and same value in another column -

-

i trying code loop in excel vba following:

i have column long date + time. have column has text string.

i want delete entire row if change in time (row - row above) less 0:00:05 , value of string same (row vs row above)

i'm running problems if condition, 5 second portion, doesn't it...

for lrow = lastrow firstrow step -1    'we check values in d column    .cells(lrow, "d")        if not iserror(.value)          if (cells(i,"d") - cells(i-1,"d")) > (0:00:05) , (cells.value(i,"f") =          cells.value(i-1,"f")         .entirerow.delete        end if    end  next lrow

ok, code above sucks. going explore different tack. want compare column f - current cell 1 below it. if same, trigger next clause - seeing if current cell in d < 0:00:05 seconds different 1 below it, if so, delete (or store information delete outside loop).

if 2 cells in f not same, skip next cell.

that make more sense? let me work code , post here.

your time need represented number dates in excel calculated number of days since given date (e.g. today 41458 days 1st jan 1900). divide day 24 1 hour , 12 5 mins example:

edited

i have re-written code following comments below changing other factors time comparison.

the code below example of how make macro automatically delete new rows added. code should added sheet making changes on (rather module or workbook). if not want take automatic approach either add option check user first or change sub , pass in range object representing cell on row has been added (let me know if need this):

private sub worksheet_change(byval target range)      dim newstr     dim newdate     dim prevstr     dim prevdate     dim interval      newstr = range("d" & target.row).value     newdate = range("f" & target.row).value     prevstr = range("d" & target.row - 1).value     prevdate = range("f" & target.row - 1).value     interval = (1 / 24) / 12 ' 5 mins      if newstr = prevstr , abs(newdate - prevdate) <= interval         target.entirerow.delete     end if  end sub

i have set test data , above seems work nicely on sample data have set up, let me know if have questions.


Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more