[VBA ] Clear Range的問題

看板Visual_Basic作者ceendy (啊嗚~)時間19年前 (2006/03/29 03:32)推噓1(1推 0噓 6→)

留言7則, 2人參與討論串1/1

我其實沒有很懂 VB 程式碼是初學者拜託前輩指教一下這個VBA程式是要篩選資料的把每天的股價資料讀進來後篩完就把這天資料clear掉但是在按clear鍵後 800行之後的資料都無法刪除超怪的請高手們幫我看一下謝謝了!!! Private Sub clear_Click() ThisWorkbook.Sheets("temp").Range("rawdata").ClearContents ThisWorkbook.Sheets("final").Range("rec").ClearContents End Sub Private Sub CommandButton1_Click() Set daterange = ThisWorkbook.Sheets("para").Range("daterange") Application.Calculation = xlCalculationManual ThisWorkbook.Sheets("temp").Range("rawdata").ClearContents Set stkrange = ThisWorkbook.Sheets("para").Range("stkrange") Set daterange = ThisWorkbook.Sheets("para").Range("daterange") Set rawdata = ThisWorkbook.Sheets("temp").Range("rawdata") Set rec = ThisWorkbook.Sheets("final").Range("rec") b = 1 While daterange.Cells(b, 1) <> "" b = b + 1 Wend c = 1 While stkrange.Cells(c, 1) <> "" c = c + 1 Wend num = 1 While rec.Cells(num, 1) <> "" num = num + 1 Wend For i = 1 To b - 1 For j = 1 To c - 1 ThisWorkbook.Sheets("temp").Range("rawdata").ClearContents With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\nsefo\" & daterange(i, 1) & ".bhv" _ , Destination:=Sheets("temp").Range("A1")) .Name = daterange(i, 1) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = False .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileOtherDelimiter = "|" .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .Refresh BackgroundQuery:=False End With 'ActiveWorkbook.Names("_" & daterange(i, 1)).Delete a = 1 While rawdata.Cells(a, 1) <> "" a = a + 1 Wend For k = 1 To a - 1 If rawdata.Cells(k, 2) = stkrange(j, 1) And rawdata.Cells(k, 3) = "FUTSTK" Then rec.Cells(num, 1) = rawdata.Cells(k, 1) rec.Cells(num, 2) = rawdata.Cells(k, 2) rec.Cells(num, 3) = rawdata.Cells(k, 3) rec.Cells(num, 4) = rawdata.Cells(k, 4) rec.Cells(num, 5) = rawdata.Cells(k, 5) rec.Cells(num, 6) = rawdata.Cells(k, 6) rec.Cells(num, 7) = rawdata.Cells(k, 7) rec.Cells(num, 8) = rawdata.Cells(k, 8) rec.Cells(num, 9) = rawdata.Cells(k, 9) rec.Cells(num, 10) = rawdata.Cells(k, 10) rec.Cells(num, 11) = rawdata.Cells(k, 11) rec.Cells(num, 12) = rawdata.Cells(k, 12) rec.Cells(num, 13) = rawdata.Cells(k, 13) rec.Cells(num, 14) = rawdata.Cells(k, 14) rec.Cells(num, 15) = rawdata.Cells(k, 15) rec.Cells(num, 16) = rawdata.Cells(k, 16) 'rec.Cells(num, 17) = rawdata.Cells(k, 17) num = num + 1 End If Next k Next j Next i Application.Calculation = xlCalculationManual End Sub -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 218.167.74.137