excel - Delete all rows in a column that don't match a value in another worksheet using VBA -


i learning vba , having trouble trying develop macro in workbook 2 worksheets. first worksheet named "para" , second named "report".

when enter number or text para!c6 (e.g. 76894), vba code delete rows in column report!y:y except 1 matches value in para!c6.

i tried following code, not working:

sub keeponlyatsymbolrows()     dim ws worksheet     dim rng range     dim lastrow long     dim s integer      s = worksheets("para").range("c6")      set ws = activeworkbook.sheets("report")      lastrow = ws.range("y" & ws.rows.count).end(xlup).row      set rng = ws.range("a1:ar" & lastrow)      ' filter , delete header row     rng         .autofilter field:=1, criteria1:="<>*s*"         .offset(1, 0).specialcells(xlcelltypevisible).entirerow.delete     end      ' turn off filters     ws.autofiltermode = false end sub

try code below.

if rng column "a" column "ar", , want filter according column "y", need filter field:=25 (not field:=1 , column a).

also, filter according value of variable, need take variable name s outside ", criteria1:="<>" & s.

code

option explicit  sub keeponlyatsymbolrows()  dim ws          worksheet dim rng         range dim lastrow     long dim s           variant  s = worksheets("para").range("c6").value  set ws = activeworkbook.sheets("report")     ws     lastrow = .range("y" & .rows.count).end(xlup).row     set rng = .range("a1:ar" & lastrow) end  ' filter , delete header row rng     .autofilter field:=25, criteria1:="<>" & s, operator:=xland     .offset(1, 0).specialcells(xlcelltypevisible).entirerow.delete end  ' turn off filters ws.autofiltermode = false  end sub

Comments

Post a Comment

Popular posts from this blog

javascript - Clear button on addentry page doesn't work -

this addentry blog page have made scratch , there underlying problems code particularly clear button supposed clear text entered when button clicked on doesn't work have saved html file problem. also 1 other problem submit button works doesn't pass of text index file have written php , reason whenever click on submit button redirects me index page without posting entry have made. <!doctype html> <html> <head> <script> function message() { alert("clear form?"); } </script> <style> body {background-color: beige;} h1 {color: black;} p {color: black;} textarea { width: 50%; height: 150px; padding: 12px 20px; box-sizing: border-box; border: 2px solid #ccc; border-radius: 4px; background-color: white; font-size: 16px; resize: none; } div { padding-left: 20px; } </style> </head> <meta charset="utf-8"> <link rel="stylesheet" href="....
Read more

python - Error: Unresolved reference 'selenium' What is the reason? -

# -*- coding: utf-8 -*- selenium import selenium import unittest, time, re class rc(unittest.testcase): def setup(self): self.verificationerrors = [] self.selenium = selenium("localhost", 4444, "*chrome", "http://stackoverflow.com/") self.selenium.start() def test_rc(self): sel = self.selenium sel.open("/") sel.type("id=kw", "selenium") sel.click("id=container") sel.click("id=su") def teardown(self): self.selenium.stop() self.assertequal([], self.verificationerrors) if __name__ == "__main__": unittest.main() this recorded selenium ide, selenium remote control code. in ide test passed.   in pycharm open, first line ( from selenium import selenium ) there error: unresolved reference 'selenium' what reason? my environment is python3.5 selenium3.3.1 selenium-server-standalone-...
Read more

ios - Pass NSDictionary from Javascript to Objective-c in JavascriptCore -

i'm using javascriptcore in app. now, i'm passing variables jscontext can passed objective-c. however, 1 of variables, nsdictionary, not passing through correctly. run code below: var evaluate = function(variables) { app.setdictionary(variables.dictionary); } this simple example has these following methods set in jscontext . this setdictionary() method: - (void)setdicationary:(nsdictionary *)dictionary { self.mutabledictionary = [dictionary mutablecopy]; } this variables.dictionary : - (nsdictionary *)dictionary { return self.values; } and how call evaluate() : jsvalue *jsfunction = self.context[@"evaluate"]; jsvalue *value = [jsfunction callwitharguments:@[self.variables]]; however, in setdictionary method, don't nsdictionary, instead nsstring containing [object object] . any ideas how can solve this? although javascriptcore automatically converts types between objective-c or swift , javascript, suggest implement ex...
Read more