Как в VBA определить нижнюю границу базы данных

[Leanna]

Есть база данных Фамилия/Имя/Оклад/Премия
1) Надо произвести суммирование Оклад+Премия = Общий заработок по всем ячейкам.
Проблема: данный отчет каждый раз применения макроса имеет одинаковое количество столбцов, но каждый раз разное количество строк.
Надо что бы общий заработок считался только в пределах базы данных.

2) Удалить те строчки, где зарплата превышает 20 000 руб.

Гипотетический пример: http://stream.ifolder.ru/4481290
Помогите пожалуйста.

[pashulka]

Ответ на Ваш вопрос, касаемо определения последней заполненной ячейки, можно найти здесь, но можно не тратить время, а просто скачать этот пример.

[Leanna]

pashulka спасибо большое за помощь, я сейчас пытаюсь овладевать VBA для эксель, и хочу понять твои записи.

With Me.Range(Me.[A2], Me.[A65536].End(xlUp)).Offset(0, 4)

1) Offset - это видимо такой способ переместиться относительно текущей ячейки (здесь 4 стобца вправо)
Что делает End я не особо поняла (пыталась поднять справку, но она на английском и это непонимаение только усилилось), для чего вообще надо указывать 2 аргумента у Range, я раньше видела только что разве Range ("A2:A65536"). А здесь получается не Selection, а выделение первой и последней ячейки..

Application.Subtotal(2, .Cells) > 0

2) Данная функция проверяет значения ячеек начиная со второго ряда и, наверное,
вниз (.Cells), являются ли они больше нуля. Я так себе это представляю.

3)Хорошо, если мне, например, в столце "F" надо вычислить долю премии в зарплате "= D2/C2"
программа мне выдает ошибку. см рисунок внизу.
Как сделать верно?

4)И последний вопрос. Если я воспользуюсь формулой iRow = Range("A1").SpecialCells(xlLastCell ).Row
Каким образом я могу всю колонку начиная с "Е2" по "Е" + iRow заполнить формулой "= C2+D2"

[pashulka]

1.

Offset - это видимо такой способ переместиться относительно текущей ячейки (здесь 4 столбца вправо)

В данном конкретном случае - относительно диапазона ячеек.

Что делает End я не особо поняла

Range("A65536").End(xlUp) - возвращает последнюю заполненную ячейку в столбце "A:A" (по сути, это эквивалент нажатию клавиш CTRL+стрелка вверх)

А здесь получается не Selection, а выделение первой и последней ячейки..

Не выделение первой и последней ячейки, а получение диапазона ячеек, начиная с ячейки "A2" и заканчивая последней заполненной ячейкой.

2. Нет, стандартная функция рабочего листа =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2; диапазон) возвращает количество ячеек содержащих числовые значения, причём с учётом фильтрации. Так что, мы просто проверяем, есть ли данные для удаления (без использования SpecialCells(xlVisible / xlCellTypeVisible))

3. .Offset(0, 1).Formula = "=D2/C2" '.Offset(, 1).Formula = "=D2/C2"

4. Me.Range("E2:E" & Me.[A1].SpecialCells(xlLastCell).Row).Form ula = "=C2+D2"

[Leanna]

pashulka я попробовала преобразовать предложенный тобою макрос для использования в своих ежедневных задачах, хочу отметить, что считает большие объёмы данных намного быстрее чем через записаный макрорекордером макросом.

Файл вложила см. ниже. Возникли почему-то проблемы с АвтоФильтром, на нем выскакивает ошибка. Почему? И как это исправить?

И вопрос по .Value, есть ли какой либо другой способ преобразовать скопом все данные из формул в значения с A1, по F-последнюю ячейку.

[pashulka]

1. В первом примере автофильтр уже наличествовал и там мы могли использовать любой из имеющихся столбцов, во втором примере, Вы устанавливаете автофильтр только в первом столбце, но применить его хотите для четвёртого, что не есть хорошо.

With .EntireRow
     .AutoFilter Field:=4, Criteria1:="0"
     .Delete
End With

2. Вы можете заменить формулы на значения только один раз, перед фильтрацией, например так : .Resize(, 6).Value = .Resize(, 6).Value
Или скопировать весь диапазон, затем в меню Правка выбрать команду Специальная вставка, затем в появившемся стандартном диалоговом окне установить переключатель напротив Значения и кликнуть кнопку Ok. (программный код Вы, можете получить, просто воспользовавшись макрорекордером, а затем, немного почистить его от мусора, такого как, например, ненужное выделение)

[Leanna]

pashulka, всё супер спасибо всё работает как надо, пришлось правда фильтр в начало текста выставить через Selection. Потому что with entire row, удаляет и строчку заголовков фильтра, из-за этого удалялись значения которые удалять не надо. В итоге получилось Database3. Я радостно ликую.

Хотелось ещё сказать что от End(xlUp)) пришлось отказаться, потому что при испытании в Primer_poslednei_yacheiki (HideFilter1 и Hidefilter2)

Sub HideSheet1() 
MsgBox Worksheets("Лист1").Range("A1").SpecialCells(xlLastCell).Row
End Sub

Sub HideSheet2()
MsgBox Worksheets("Лист1").[A65000].End(xlUp)
End Sub

HideFilter1 правильно выдает конец массива(18), а HideSheet2 выдает значение (7).

[pashulka]

Оба варианта дают правильные результаты (несмотря на то, что в первом примере речь идёт о номере строки, а во втором о значении ячейки, что далеко не одно и тоже), только нужно учитывать, что все они имеют свои особенности, в т.ч. и CTRL+END, и CTRL+стрелка вверх/вниз/и т.д.
Так, например, применение SpecialCells(xlLastCell) (и не только) приводит к тому, что выполняется событие Worksheet_SelectionChange() (и аналогичные события книги, приложения), причём в случае с последней ячейкой, это событие будет вызвано дважды. С этим, кстати, можно бороться (см. ниже) но лучше применять тот или иной метод в зависимости от ситуации, а пред'явленный гипотетический пример не содержал и намёка на то, что данные, которые требуется обработать могут находиться в скрытых ячейках.

Application.EnableEvents = False
iRow = Worksheets(1).[A1].SpecialCells(xlLastCell).Row
Application.EnableEvents = True

[Zevs_Shah]

Здравствуйте!
Прошу Вас помогите ПОЖАЛУЙСТА решить 5-задач на ПАСКАЛЕ. Я вообще ничего не смыслю в этой сфере...
- На основании анкетных данных студентов и итогов экзаменационных сессий определить:
1) Фамилии студентов из г. Москвы и процент их числа от общего числа студентов в группе.
2) Фамилии студентов не являющихся жителями г. Москвы, сдавших сессию с одной неудовлетворительной оценкой.
3) Определить списки студентов, сдавших сессию с одной тройкой.
4) Фамилии отличников, жителей г. Москвы и их процент от общего числа отличников в группе.
5) Определить список отличников жителей г. Москвы и их процент от общего числа отличников.
Надеюсь на Вашу помощь.