英文:
how to count filtered columns in a table
问题
这段代码运行正常，但当筛选后的数据为空时，仍会复制标题行。如果将-1传递给它，它根本不会复制任何数据。你可以帮助我吗？
英文:
I'm trying to make this code works
For Each dest In destSS
Hoja8.ListObjects("BD").range.AutoFilter Field:=9, Criteria1:= _
Array(dest), Operator:=xlFilterValues
Hoja8.ListObjects("BD").range.AutoFilter Field:=10, Criteria1:= _
"<=.8", Operator:=xlFilterValues
With Hoja8.ListObjects("BD").Sort
.SortFields.Clear
.SortFields.Add Key:=range("BD[Load Status]"), Order:=xlDescending
.SortFields.Add Key:=range("BD[Destination]"), Order:=xlAscending
.SortFields.Add Key:=range("BD[Current Location]"), Order:=xlAscending
.SortFields.Add Key:=range("BD[Event]"), Order:=xlAscending
.SortFields.Add Key:=range("BD[Dwell]"), Order:=xlDescending
.Apply
End With
On Error Resume Next
Set myRange = Hoja8.ListObjects("BD").DataBodyRange.CurrentRegion.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If myRange Is Nothing Then
'Do nothing
Else
'do stuff
Sheets("BD").Activate
range("A1").Select
With Hoja8.ListObjects("BD")
Union(.ListColumns(9).range, _
.ListColumns(1).range, _
.ListColumns(3).range, _
.ListColumns(5).range, _
.ListColumns(8).range, _
.ListColumns(10).range, _
.ListColumns(15).range, _
.ListColumns(17).range, _
.ListColumns(19).range).Select
Selection.Copy
Sheets("Recently Placed").Select
'check ifs there any data in the sheet and if there are, find the last row to paste the data--------------
If WorksheetFunction.CountA(range("C1").CurrentRegion) = 0 Then
range("C1").Select
ActiveSheet.Paste
Else
lscol = range("C" & rows.count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
End If
End With
Set myRange = Nothing
End If
Next
columns("G").Cut
columns("C").Insert
Call SortDB
Application.ScreenUpdating = True
End Sub
It runs fine but when the filtered data shows nothing it's still copying the header and when I put a -1 it doesn't copy any data at all, can you help me out here?
I tried this code and runs but when the filter shows nothing on the rows still copies the header
答案1
得分: 2
' 在循环中重置对象变量
'
' 由于此代码位于循环中，您需要在属于“如果myRange为Nothing”部分的“End If”之前重置myRange，使用以下代码行“Set myRange = Nothing”。
For Each dest In destSS
' 做一些操作
On Error Resume Next
Set myRange = sheet8.ListObjects("BD").DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If myRange Is Nothing Then
' 什么都不做
Else
' 做一些操作
Set myRange = Nothing ' 就在这里！！！
End If
Next dest
英文:
'Reset' Object Variable When In a Loop
- Since this code is in a loop, you want to reset
myRangei.e. right before theEnd Ifthat belongs toIf myRange Is Nothing, use the lineSet myRange = Nothing.
<!-- language: lang-vb -->
For Each dest In destSS
' Whatever
On Error Resume Next
Set myRange = sheet8.ListObjects("BD").DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If myRange Is Nothing Then
' do nothing
Else
' do stuff
Set myRange = Nothing ' right here!!!
End If
Next dest
答案2
得分: 0
I'm sorry, but it seems that the provided code contains a mixture of code-related elements and text that should be translated. To assist you better, please provide the specific text that you would like to have translated.
英文:
Try the following code...
On Error Resume Next
With sheet8.ListObjects("BD").Range
Set myRange = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0
If Not myRange Is Nothing Then
'example to copy filtered data, including headers
sheet8.ListObjects("BD").Range.Copy Sheet2.Range("a1")
'example to copy filtered data, excluding headers
myRange.Copy Sheet3.Range("a1")
End If
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论