日记首页
|
列表
|
添加日记
|
管理登陆
标题：搜索-筛选代码
<div style="background: rgb(243, 243, 243); margin: 5px 20px; padding: 5px; border: 1px solid rgb(204, 204, 204); border-image: none;"><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br>Public Sub MySelRows()<br>Dim cnn, SQL$ '定义数据库连接和SQL语句</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">Set cnn = CreateObject("adodb.connection") '创建数据库连接<br>Set rs = CreateObject("adodb.recordset") '创建一个数据集保存数据</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;HDR=yes;IMEX=2';data source=" & ThisWorkbook.FullName</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">SQL = "select * from [sheet1$] where 受理人 Like '%zuoxi%' Or 受理人 Like '&dudao%'"</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">Set rs = cnn.Execute(SQL)</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">Range("A2").CopyFromRecordset rs</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">Set cnn = Nothing<br>Set rs = Nothing</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br>End Sub</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">Public Sub tt()<br>Set cnn = Nothing<br>Set rs = Nothing<br>End Sub</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();"><br>Sub 删除行() '数组<br> Dim nRow&, m&, Arr(), Brr()<br> With Sheet1<br> nRow = .Range("a65536").End(xlUp).Row<br> Arr = .Range("a2:g" & nRow).Value '共有G列<br> ReDim Brr(1 To nRow, 1 To 7)<br> For i = 1 To nRow - 1<br> If Arr(i, 5) Like "*zuoxi*" Or Arr(i, 5) Like "*dudao*" Then '第*列筛选"zuoxi*" Or Arr(i, 5) Like "dudao*"<br> m = m + 1<br> For j = 1 To 7<br> Brr(m, j) = Arr(i, j)<br> Next<br> End If<br> Next<br> .Range("a2:g" & nRow).Value = Brr<br> End With<br>End Sub</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();">'忽略了大写</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();"><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();"><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();">Sub 删除行2()<br>Dim c%, i%<br>c = Cells(Rows.Count, 5).End(3).Row '取第*列第一次出现的非空单元格的行号<br>For i = c To 1 Step -1<br>If Cells(i, 5) Like "*否*" Then<br>Rows(i).Delete<br>End If<br>Next<br>End Sub<br></div></div>
