Здравствуйте, подскажите, пожалуйста, почему вставка формулы не прекращается после последней видимой строки, несмотря на SpecialCells(xlCellTypeVisible), а продолжается до самой последней строки Excel, около 60 с чем-то тысяч там.
---------------------------------------------------------------------------------------------------------------
Range("H2:H" & R).AutoFilter Field:=8, Criteria1:="=*C*", Operator:=xlFilterValues ' Оставляются через фильтр строки, которые содержат в диаметре букву "С"
Dim cRange As Range
Dim b As Range
Set cRange = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible) ' Эта формула выбирает все видимые ячейки в непрерывном диапазоне (CurrentRegion) вокруг активной ячейки
For Each b In cRange.Rows
If b.Cells(9) Like "*/*" Then
Else
b.Cells(8).Replace What:="C", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 ' В выбранном фильтром столбце удаляется буква "C"
End If
Next
ActiveSheet.ShowAllData
Range("H2:H" & R).AutoFilter Field:=8, Criteria1:="<>*C*", Operator:=xlFilterValues ' Оставляются через фильтр строки, которые не содержат в диаметре букву "С"
Columns("E:E").ClearContents ' Очищаются ячейки колонки с надписью "легкогрузовая"
ActiveSheet.ShowAllData
Range("L2:L" & R).AutoFilter Field:=12, Criteria1:="шип", Operator:=xlFilterValues ' Оставляются через фильтр строки, которые не содержат в диаметре букву "С"
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete ' Удаляет выбранные фильтром строки
ActiveSheet.ShowAllData
R = Cells(Rows.Count, 1).End(xlUp).Row
Range("H2:H" & R).AutoFilter Field:=8, Criteria1:="=*C*", Operator:=xlFilterValues ' Оставляются через фильтр строки, которые содержат в диаметре букву "С"
Set cRange = ActiveCell.SpecialCells(xlCellTypeVisible) ' Эта формула выбирает все видимые ячейки в текущей области активной ячейки
For Each b In cRange.Rows
b.Cells(15).FormulaR1C1 = "=RC[1]*1.08"
Next
End Sub
===================================
Первый раз (в начале кода) операции после фильтра проходят хорошо, третий же раз, где вставляется формула (в конце кода) .... начинает считать до 60 000 строк. А в фильтре всего 214 видимых строк.
Какой адрес сообщает MsgBox:
Sub Макрос()
MsgBox ActiveCell.CurrentRegion.Address
End Sub
$A1$1:$J$4861
В этом коде на первый взгляд что-то не то:
Set cRange = ActiveCell.SpecialCells(xlCellTypeVisible) ' Эта формула выбирает все видимые ячейки в текущей области активной ячейки
For Each b In cRange.Rows
b.Cells(15).FormulaR1C1 = "=RC[1]*1.08"
Next
Здесь у вас SpecialCells(xlCellTypeVisible) применяется не к ActiveCell.CurrentRegion, а просто к ActiveCell.
Да, потому что если добавить CurrentRegion, то считать начинает не в 15 столбце, а в 5-м. Потому что между 10-м и 14-м столбцом идут пустые столбцы. А по Region может считать только смежные, неразрывные области. И при попытке написать 15, 16, 17 столбцы в Cell, считать начинает соответственно в 5,6,7 столбцах.
Если уменьшить код и оставить только проблемный фрагмент, то ошибка остаётся?
Код
Sub Macro()
Dim cRange As Range
Dim b As Range
r = Cells(Rows.Count, 1).End(xlUp).row
Range("H2:H" & r).AutoFilter Field:=8, Criteria1:="=*C*", Operator:=xlFilterValues ' Оставляются через фильтр строки, которые содержат в диаметре букву "С"
Set cRange = ActiveCell.SpecialCells(xlCellTypeVisible) ' Эта формула выбирает все видимые ячейки в текущей области активной ячейки
For Each b In cRange.Rows
b.Cells(15).FormulaR1C1 = "=RC[1]*1.08"
Next
End Sub
Да. Просчёт дошёл до 53 000 строк, я остановил.
Этот код выдаст в View - Immediate Window адреса. Какие адреса выдал код?
Код
Sub Macro()
Dim cRange As Range
Dim b As Range
r = Cells(Rows.Count, 1).End(xlUp).row
Range("H2:H" & r).AutoFilter Field:=8, Criteria1:="=*C*", Operator:=xlFilterValues
Set cRange = ActiveCell.SpecialCells(xlCellTypeVisible)
Debug.Print ActiveCell.Address
Debug.Print cRange.Address
For Each b In cRange.Rows
b.Cells(15).FormulaR1C1 = "=RC[1]*1.08"
Next
End Sub
Не пойму никак .... С таким кодом, именно сам по себе это кусок
Sub Macro()
Dim cRange As Range
Dim b As Range
Dim R As Long
R = Cells(Rows.Count, 1).End(xlUp).Row
Range("H2:H" & R).AutoFilter Field:=8, Criteria1:="=*C*", Operator:=xlFilterValues
Set cRange = ActiveCell.SpecialCells(xlCellTypeVisible)
Debug.Print ActiveCell.Address
Debug.Print cRange.Address
For Each b In cRange.Rows
b.Cells(15).FormulaR1C1 = "=RC[1]*1.08"
Next
End Sub
макрос не проходит. Выдаёт ошибку 1004 : "Метод AutoFilter из класса Range завершён неверно"
Field:=8 - под 8 вы какой столбец подразумеваете (какая буква столбца)?
H
Эта команда создаёт фильтр в столбце H:
Range("H2:H" & R).AutoFilter
Соответственно, фильтр будет находиться только в столбце H, а не со столбца A по H (или по другой столбец).
Параметр Field не связан со столбцами Excel, у AutoFilter своя нумерация столбцов.
Т.к. в фильтре только один столбец, а вы указываете столбец 8, то происходит ошибка, т.к. в фильтре нет столбца 8.
Спасибо !
Вот адреса:
$A$1
$1:$2,$414:$427,$469:$490,$623:$648,$727:$727,$743:$756,$986:$987,$1312:$1312,$1854:$1856,$1888:$2033,$2077:$2080,$2147:$2189,$2196:$2196,$2201:$2207,$2246:$2332,$2341:$2424,$2449:$2455,$2463:$2463,$2587:$2597,$2639:$2639,$2782:$2790,$2920:$2924,$2971:$2971
На первый взгляд что-то не то. Фильтр вы ставите со строки 2, а в результат попадает и строка 1.
Не знаю. В файле нет пустых строк вначале. Первая строка идёт уже заполненная.
Та строка, куда ставится фильтр, не учитывается им: надо будет вставлять пустую строку, наверное, чтобы считал с первой.
Но пока суть в том, что цикл идёт не по последнюю заполненную отфильтрованную строку, а до конца Excel
Почему вы фильтр применяете со строки 2? В фильтр включается и шапка таблицы.
У вас шапка начинается с какой строки? С 1 или со 2?
Шапки нет. Строки со значением идут с первой строки. Я вставлял пустую строку в коде раньше, поэтому фильтр начинал со 2 строки.
В фильтр нужно включать и шапку. Если вы указываете строку 2, то строка 2 не участвует в фильтре.
Да, уже понял. А что с просчётом ?
Сделайте человеческий код. Вторая строка ведь тоже должна участвовать в расчётах?
Сделайте так, чтобы фильтр создавался со строки 1.
Иначе сейчас вообще не понятно, что у вас там происходит.
Не пойму ....
Вот ставлю поле фильтра = 1, как только для одного указанного столбца F. НО. При пошаговом выполнении кода видно, что фильтр ставится вовсе не в F, а в первый столбец (А), и фильтрует там ....... Почему фильтрует не в F ?
Dim r As Long
r = Cells(Rows.Count, 1).End(xlUp).Row
Range("F1:F" & r).AutoFilter Field:=1, Criteria1:="<135", Operator:=xlFilterValues
Если фильтр применён не к одному столбцу (в данном случае к F), а к нескольким: от A до F и т.д. (стрелочки в Экселе находятся не в одном столбце, а в нескольких), то.
В коде вы указываете столбец F, но это игнорируется фильтром, а фильтр рассматривает всю область, где есть фильтр. Соответственно Field будет относительно всего диапазона, к которому применён фильтр.
Исправил код.
Всё осталось так же: формулу ставит в строки до конца Excel, а не только в видимые.
Запускаю Ваш код:
Sub Macro()
Dim cRange As Range
Dim b As Range
Dim R As Long
R = Cells(Rows.Count, 1).End(xlUp).Row
Range("H1:H" & R).AutoFilter Field:=1, Criteria1:="=*C*", Operator:=xlFilterValues
Set cRange = ActiveCell.SpecialCells(xlCellTypeVisible)
Debug.Print ActiveCell.Address
Debug.Print cRange.Address
For Each b In cRange.Rows
b.Cells(15).FormulaR1C1 = "=RC[1]*1.08"
Next
End Sub
Получаю в Immediate Window:
$A$1
$1:$1,$414:$427,$469:$490,$623:$648,$727:$727,$743:$756,$986:$987,$1312:$1312,$1854:$1856,$1888:$2033,$2077:$2080,$2147:$2189,$2196:$2196,$2201:$2207,$2246:$2332,$2341:$2424,$2449:$2455,$2463:$2463,$2587:$2597,$2639:$2639,$2782:$2790,$2920:$2924,$2971:$2971
Нужно обращаться к диапазону фильтра, чтобы внутри этого диапазона работать с видимыми строками.
Т.е. сделайте так, как вы раньше работали с видимыми строками фильтра (вы на форуме задавали вопрос, в котором тоже была работа с видимыми строками фильтра). Только нужно учитывать, что Offcet(1) исключит шапку, но включит строку, которая находятся под таблицей. В этом случае нужно ещё внести изменения в код, который обращается к телу фильтра.
Код:
Sub Macro()
Dim cRange As Range
Dim b As Range
Dim R As Long
R = Cells(Rows.Count, 1).End(xlUp).row
Range("H1:H" & R).AutoFilter Field:=1, Criteria1:="=*C*", Operator:=xlFilterValues
Set cRange = ActiveSheet.AutoFilter.Range.Offset(1).Columns(1).SpecialCells(xlCellTypeVisible).EntireRow
Debug.Print ActiveCell.Address
Debug.Print cRange.Address
For Each b In cRange.Rows
b.Cells(15).FormulaR1C1 = "=RC[1]*1.08"
Next
End Sub
Дописал в ответ пояснения и в код внёс изменения.
Спасибо, заработало ! Вы хорошо объясняете.
"но включит строку, которая находятся под таблицей. В этом случае нужно ещё внести изменения в код, который обращается к телу фильтра."
Вот к телу:
Range("H1:H" & R).AutoFilter Field:=1, Criteria1:="=*C*", Operator:=xlFilterValues
А какие изменения ?
Вот так можно исключить строку, которая находится под таблицей.
Код:
Sub Macro()
Dim cRange As Range
Dim b As Range
Dim R As Long
R = Cells(Rows.Count, 1).End(xlUp).row
Range("H1:H" & R).AutoFilter Field:=1, Criteria1:="=*C*", Operator:=xlFilterValues
Set cRange = ActiveSheet.AutoFilter.Range.Offset(1).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1) _
.Columns(1).SpecialCells(xlCellTypeVisible).EntireRow
Debug.Print ActiveCell.Address
Debug.Print cRange.Address
For Each b In cRange.Rows
b.Cells(15).FormulaR1C1 = "=RC[1]*1.08"
Next
End Sub
Извините. Но одну строчку после последней видимой формула всё равно посчитала:
Set cRange = ActiveSheet.AutoFilter.Range.Offset(1).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1).Columns(1).SpecialCells(xlCellTypeVisible).EntireRow
Не знаю, почему.
Какой адрес даёт этот код, совпадает с адресом вашей таблицы?
Sub Macro()
Debug.Print ActiveSheet.AutoFilter.Range.Offset(1).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1).Address
End Sub
$H$2:$H$4861
Это область столбца даёт.
Тело таблицы находится в строках 2 - 4861?
Да. А на 4862 стоит 0.
Если Debug.Print показал правильные номера строк, значит код работает с телом таблицы и не выходит ниже таблицы.
И 0 появляется после прохождение цикла с формулой. Значит формула каким-то образом захватывает эту строку.
Строка 4862 не пустая, поэтому она тоже входит в диапазон фильтра.
Так тело же находится в строках 2 - 4861. Как тогда 4862 не пустая ?
Вы пишите, что в строке 4862 находится ноль.
Да. Как результат просчёта формулы по соседнему столбцу. Но в соседнем столбце в этой строке, как и в других строках, нет ничего. То есть таблица заканчивается последней заполненной данными строкой с номером 4861.
Если нажать Ctrl + End, в какую строку переходит курсор? Если дальше, чем нужно, то удалите все строки, которые находятся под таблицей. Возможно в этих строках что-то есть, но это невидимо глазу.
До выполнения макроса = 5313, после выполнения = 5314.
А что там может быть, что невидимо ?
Если до запуска макроса нажать Ctrl+End, в какую строку переходит курсор?
Если после макроса нажать Ctrl+End, в какую строку переходит курсор?
Я и написал выше, что до запуска макроса переходит в строку под № 5313, после выполнения макроса, в строку под № 5314.
Вы только этот код используете? Или в начале есть ещё ваш код?
Код:
Sub Macro()
Dim cRange As Range
Dim b As Range
Dim R As Long
R = Cells(Rows.Count, 1).End(xlUp).row
Range("H1:H" & R).AutoFilter Field:=1, Criteria1:="=*C*", Operator:=xlFilterValues
Set cRange = ActiveSheet.AutoFilter.Range.Offset(1).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1) _
.Columns(1).SpecialCells(xlCellTypeVisible).EntireRow
Debug.Print ActiveCell.Address
Debug.Print cRange.Address
For Each b In cRange.Rows
b.Cells(15).FormulaR1C1 = "=RC[1]*1.08"
Next
End Sub
Чисто по Вашему коду после макроса Ctrl+End переводит курсор в строку № 5313.
С моим полным кодом — в строку под № 5314 (я добавляю сверху пустую строку в своём коде).
Только Ваш код показывает последнюю заполненную строку (просто её номер 5313 был до выполнения макроса. А видимый он по счёту 758), а мой код показывает после выполнения и нажатия Ctrl+End пустую строку с № 5314 (там же в коде строки удаляются).
Значит ваш код что-то делает не то.
Весь ваш код я не знаю.
Спасибо ! Нашёл свою ошибку, я пропустил поставить в одном из фильтров по этой колонке Count - 1. Теперь всё работает !