Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Не выделение пустых ячеек - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Не выделение пустых ячеек (Формулы/Formulas)
Не выделение пустых ячеек
argetlam Дата: Суббота, 09.04.2016, 17:16 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 46
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Доброе время суток.
Столкнулся с проблемой невозможности заполнить пустые ячейки оставшиеся от сводной таблицы, подробно она описана тут.
Суть в том, что у 2007 экселя есть ограничение по оперативке, я подозреваю из за этого при попытке выделить больше определенного количества "пустых" ячеек через F5 выдает ошибку.
Соответственно код типа такого не работает:
[vba]
Код
Sub Zapolnenye_Null()
        n = Selection.Address
On Error GoTo A
        g = 1 / (Range(n).Cells.Count - 1)
        Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        Range(n) = Range(n).Value
A:     Exit Sub
End Sub
[/vba]
Прошу помощи, есть ли способ обойти это ограничение или заполнить пустые ячейки как-то иначе?
Файл не прикладываю ибо массив необходимый для отражения проблемы просто не вписывается в 100 кб. Для эксперимента: есть 72000 строк, из них заполнено 31000, в данном случае выделить только пустые не получается, выдает ошибку "Выделенная область слишком велика".


Сообщение отредактировал argetlam - Суббота, 09.04.2016, 17:25
 
Ответить
СообщениеДоброе время суток.
Столкнулся с проблемой невозможности заполнить пустые ячейки оставшиеся от сводной таблицы, подробно она описана тут.
Суть в том, что у 2007 экселя есть ограничение по оперативке, я подозреваю из за этого при попытке выделить больше определенного количества "пустых" ячеек через F5 выдает ошибку.
Соответственно код типа такого не работает:
[vba]
Код
Sub Zapolnenye_Null()
        n = Selection.Address
On Error GoTo A
        g = 1 / (Range(n).Cells.Count - 1)
        Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        Range(n) = Range(n).Value
A:     Exit Sub
End Sub
[/vba]
Прошу помощи, есть ли способ обойти это ограничение или заполнить пустые ячейки как-то иначе?
Файл не прикладываю ибо массив необходимый для отражения проблемы просто не вписывается в 100 кб. Для эксперимента: есть 72000 строк, из них заполнено 31000, в данном случае выделить только пустые не получается, выдает ошибку "Выделенная область слишком велика".

Автор - argetlam
Дата добавления - 09.04.2016 в 17:16
Karataev Дата: Суббота, 09.04.2016, 17:21 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 887
Репутация: 334 ±
Замечаний: 0% ±

Excel
В VBE желтым цветом какая строка в коде выделяется?


 
Ответить
СообщениеВ VBE желтым цветом какая строка в коде выделяется?

Автор - Karataev
Дата добавления - 09.04.2016 в 17:21
argetlam Дата: Суббота, 09.04.2016, 17:25 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 46
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Karataev, фишка в том что код VBA отрабатывает, но в результате мы получаем значение первой ячейки протянутое до конца столбца.
 
Ответить
СообщениеKarataev, фишка в том что код VBA отрабатывает, но в результате мы получаем значение первой ячейки протянутое до конца столбца.

Автор - argetlam
Дата добавления - 09.04.2016 в 17:25
Karataev Дата: Суббота, 09.04.2016, 17:27 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 887
Репутация: 334 ±
Замечаний: 0% ±

Excel
Удалите пока из кода строку, чтобы увидеть, в какой строке ошибка
[vba]
Код
On Error GoTo A
[/vba]


 
Ответить
СообщениеУдалите пока из кода строку, чтобы увидеть, в какой строке ошибка
[vba]
Код
On Error GoTo A
[/vba]

Автор - Karataev
Дата добавления - 09.04.2016 в 17:27
argetlam Дата: Суббота, 09.04.2016, 17:44 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 46
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Karataev, Говорю же: VBA не выдает ошибки. Совсем. Он работает, но результат совершенно не тот, который должен быть.
Сейчас я пользуюсь кодом типа:
[vba]
Код
sub test()

Dim wbKO As Workbook
Dim wsWS As Worksheet
sFileName = Application.GetOpenFilename("Excel файлы (*.xls; *.xlsx; *.xlsm),*.xls;*.xslx;*.xlsm")
Set wbKO = Workbooks.Open(sFileName)
Set wsWS = wbKO.Worksheets(1)

wsWS.Range("C2", "C" + Replace(Str(wsWS.Cells(wsWS.Rows.Count, 5).End(xlUp).Row), " ", "")).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

end sub
[/vba]
Но для .SpecialCells(xlCellTypeBlanks) обрабатываемый массив слишком большой.


Сообщение отредактировал argetlam - Суббота, 09.04.2016, 17:46
 
Ответить
СообщениеKarataev, Говорю же: VBA не выдает ошибки. Совсем. Он работает, но результат совершенно не тот, который должен быть.
Сейчас я пользуюсь кодом типа:
[vba]
Код
sub test()

Dim wbKO As Workbook
Dim wsWS As Worksheet
sFileName = Application.GetOpenFilename("Excel файлы (*.xls; *.xlsx; *.xlsm),*.xls;*.xslx;*.xlsm")
Set wbKO = Workbooks.Open(sFileName)
Set wsWS = wbKO.Worksheets(1)

wsWS.Range("C2", "C" + Replace(Str(wsWS.Cells(wsWS.Rows.Count, 5).End(xlUp).Row), " ", "")).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

end sub
[/vba]
Но для .SpecialCells(xlCellTypeBlanks) обрабатываемый массив слишком большой.

Автор - argetlam
Дата добавления - 09.04.2016 в 17:44
Karataev Дата: Суббота, 09.04.2016, 18:43 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 887
Репутация: 334 ±
Замечаний: 0% ±

Excel
При использовании макроса из поста 5, что макрос вставляет в столбец C в пустые ячейки?


 
Ответить
СообщениеПри использовании макроса из поста 5, что макрос вставляет в столбец C в пустые ячейки?

Автор - Karataev
Дата добавления - 09.04.2016 в 18:43
Gustav Дата: Суббота, 09.04.2016, 18:59 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1410
Репутация: 541 ±
Замечаний: 0% ±

начинал с Excel 4.0...
argetlam, то, что эффектно выполняется вручную и на небольшой сводной таблице (точнее, на ее результатах), не всегда срабатывает при буквальном перекладывании алгоритма на большие объемы. Вы сегодня как раз с этим столкнулись. Но раз уж расчехлен макроинструментарий, так ему и поручите массовое выполнение элементарных операций:
[vba]
Код
Sub io()
    Dim rng As Range, c As Range
    
    Set rng = Range("A2:K72000")
    For Each c In rng.Cells
        If IsEmpty(c) Then c = c.Offset(-1)
    Next
End Sub
[/vba]
Минута времени выполнения - и тема закрыта.

Но для .SpecialCells(xlCellTypeBlanks) обрабатываемый массив слишком большой.
Подозреваю, что тут срабатывает техническое ограничение Excel: "Количество выделенных диапазонов, не более - 2 048" (можно погуглить по фразе "Excel технические характеристики").


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Суббота, 09.04.2016, 19:08
 
Ответить
Сообщениеargetlam, то, что эффектно выполняется вручную и на небольшой сводной таблице (точнее, на ее результатах), не всегда срабатывает при буквальном перекладывании алгоритма на большие объемы. Вы сегодня как раз с этим столкнулись. Но раз уж расчехлен макроинструментарий, так ему и поручите массовое выполнение элементарных операций:
[vba]
Код
Sub io()
    Dim rng As Range, c As Range
    
    Set rng = Range("A2:K72000")
    For Each c In rng.Cells
        If IsEmpty(c) Then c = c.Offset(-1)
    Next
End Sub
[/vba]
Минута времени выполнения - и тема закрыта.

Но для .SpecialCells(xlCellTypeBlanks) обрабатываемый массив слишком большой.
Подозреваю, что тут срабатывает техническое ограничение Excel: "Количество выделенных диапазонов, не более - 2 048" (можно погуглить по фразе "Excel технические характеристики").

Автор - Gustav
Дата добавления - 09.04.2016 в 18:59
argetlam Дата: Суббота, 09.04.2016, 19:57 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 46
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Karataev, Вставляет значение из предыдущей ячейки.
Gustav, отличное решение, я пытался выдумать что-то такое, но знаний у меня хватает только на макрорекордер ^_^ Большое спасибо, проблема решена! А код в библиотеку эталонных решений (после добавления динамического диапазона вместо Range).
Подозреваю, что тут срабатывает техническое ограничение Excel: "Количество выделенных диапазонов, не более - 2 048" (можно погуглить по фразе "Excel технические характеристики").

Ну я подозревал что-то такое, спасибо за уточнение.

UPD
Вот такой код меня вполне устроил, отличная замена коду в шапке:
[vba]
Код
Sub test()
    Dim wbWB As Worksheet
    Dim rng As Range, c As Range
    Set wbWB = Application.ActiveWorkbook.ActiveWorksheet
    Set rng = wbWB.Range("A2", "C" + Replace(Str(wbWB.Cells(wbWB.Rows.Count, 5).End(xlUp).Row), " ", "")) 'подразумеваютcя область от A2 до последней заполненной ячейки С. Последняя ячейка считается по 5 столбцу

    For Each c In rng.Cells
        If IsEmpty(c) Then c = c.Offset(-1)
    Next
End sub
[/vba]

Портянку с Range в принципе можно заменить на Cells(1,1).CurrentRegion, но тогда пропуски в числовых значениях тоже будут заполнены, что не есть корректно. Однако если различных сумм не было в сводной - такое решение допустимо.


Сообщение отредактировал argetlam - Суббота, 09.04.2016, 20:26
 
Ответить
СообщениеKarataev, Вставляет значение из предыдущей ячейки.
Gustav, отличное решение, я пытался выдумать что-то такое, но знаний у меня хватает только на макрорекордер ^_^ Большое спасибо, проблема решена! А код в библиотеку эталонных решений (после добавления динамического диапазона вместо Range).
Подозреваю, что тут срабатывает техническое ограничение Excel: "Количество выделенных диапазонов, не более - 2 048" (можно погуглить по фразе "Excel технические характеристики").

Ну я подозревал что-то такое, спасибо за уточнение.

UPD
Вот такой код меня вполне устроил, отличная замена коду в шапке:
[vba]
Код
Sub test()
    Dim wbWB As Worksheet
    Dim rng As Range, c As Range
    Set wbWB = Application.ActiveWorkbook.ActiveWorksheet
    Set rng = wbWB.Range("A2", "C" + Replace(Str(wbWB.Cells(wbWB.Rows.Count, 5).End(xlUp).Row), " ", "")) 'подразумеваютcя область от A2 до последней заполненной ячейки С. Последняя ячейка считается по 5 столбцу

    For Each c In rng.Cells
        If IsEmpty(c) Then c = c.Offset(-1)
    Next
End sub
[/vba]

Портянку с Range в принципе можно заменить на Cells(1,1).CurrentRegion, но тогда пропуски в числовых значениях тоже будут заполнены, что не есть корректно. Однако если различных сумм не было в сводной - такое решение допустимо.

Автор - argetlam
Дата добавления - 09.04.2016 в 19:57
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Не выделение пустых ячеек (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!