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

Вход

Регистрация

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

 

= Мир MS Excel/Макрос копирует пустые строки при переносе данных в Excel - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Макрос копирует пустые строки при переносе данных в Excel (Макросы/Sub)
Макрос копирует пустые строки при переносе данных в Excel
Callione Дата: Пятница, 18.03.2016, 18:40 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Добрый день.
Помогите разобраться плз.
Ниже кусок макроса, в рамках которого у меня перемещаются данные из файла All_data (во вложении) во второй файл Excel.
При этом (смотрим вложенный файл), если в перемещаемой ячейке листа "All" копируется строка 2 (есть текст), то проблем не возникает.
А вот если копируются все остальные строки (например 3 и далее), в которых нет текста, но содержатся формулы, то во втором файле создается пустая строка, чего мне важно избежать! Формулами в Excel я умею это делать, используя ДЛСТР(A1)>0, а вот в VBA не силен...
Ситуация в том, что количество строк в закладках "data" постоянно меняется и поэтому приходится формулами прятать нули и Н/Д на закладке Алл, т.к. она служит отчетом.

Вот кусок макроса из второго файла. Как сделать так, чтобы он не тянул данные, где нет текста и есть формулы?
[vba]
Код
' будем брать данные с первого листа
Set sh = WB.Worksheets(1)

' берем диапазон ячеек с ячейки A2 до последней заполненной в столбце A
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & sh.Rows.Count).End(xlUp)).Resize(, 14)

' ==== переносим данные в наш файл (shd - кодовое имя листа, куда помещаем данные)
shd.Range("b" & shd.Rows.Count).End(xlUp).Offset(1).Resize(ra.Rows.Count, ra.Columns.Count).Value = ra.Value
' ==== конец обработки данных из очередного файла
[/vba]
К сообщению приложен файл: All_data.xlsx(13Kb)


Сообщение отредактировал Callione - Пятница, 18.03.2016, 18:45
 
Ответить
СообщениеДобрый день.
Помогите разобраться плз.
Ниже кусок макроса, в рамках которого у меня перемещаются данные из файла All_data (во вложении) во второй файл Excel.
При этом (смотрим вложенный файл), если в перемещаемой ячейке листа "All" копируется строка 2 (есть текст), то проблем не возникает.
А вот если копируются все остальные строки (например 3 и далее), в которых нет текста, но содержатся формулы, то во втором файле создается пустая строка, чего мне важно избежать! Формулами в Excel я умею это делать, используя ДЛСТР(A1)>0, а вот в VBA не силен...
Ситуация в том, что количество строк в закладках "data" постоянно меняется и поэтому приходится формулами прятать нули и Н/Д на закладке Алл, т.к. она служит отчетом.

Вот кусок макроса из второго файла. Как сделать так, чтобы он не тянул данные, где нет текста и есть формулы?
[vba]
Код
' будем брать данные с первого листа
Set sh = WB.Worksheets(1)

' берем диапазон ячеек с ячейки A2 до последней заполненной в столбце A
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & sh.Rows.Count).End(xlUp)).Resize(, 14)

' ==== переносим данные в наш файл (shd - кодовое имя листа, куда помещаем данные)
shd.Range("b" & shd.Rows.Count).End(xlUp).Offset(1).Resize(ra.Rows.Count, ra.Columns.Count).Value = ra.Value
' ==== конец обработки данных из очередного файла
[/vba]

Автор - Callione
Дата добавления - 18.03.2016 в 18:40
SLAVICK Дата: Пятница, 18.03.2016, 19:01 | Сообщение № 2
Группа: Модераторы
Ранг: Старожил
Сообщений: 1841
Репутация: 614 ±
Замечаний: 0% ±

2007,2010,2013,2016
Попробуйте так:
[vba]
Код
n = Application.WorksheetFunction.Match("", Columns("a"), 0)
' берем диапазон ячеек с ячейки A2 до последней заполненной в столбце A
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & n)).Resize(, 14)
[/vba]


Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеПопробуйте так:
[vba]
Код
n = Application.WorksheetFunction.Match("", Columns("a"), 0)
' берем диапазон ячеек с ячейки A2 до последней заполненной в столбце A
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & n)).Resize(, 14)
[/vba]

Автор - SLAVICK
Дата добавления - 18.03.2016 в 19:01
_Boroda_ Дата: Пятница, 18.03.2016, 19:49 | Сообщение № 3
Группа: Модераторы
Ранг: Экселист
Сообщений: 9369
Репутация: 3942 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А вот так не прокатит? (если в data1данные заполнены по порядку, без промежутков)
[vba]
Код
Set ra = sh.Range("a2").Resize(WorksheetFunction.Count(sh.Columns(3)), 14)
[/vba]


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА вот так не прокатит? (если в data1данные заполнены по порядку, без промежутков)
[vba]
Код
Set ra = sh.Range("a2").Resize(WorksheetFunction.Count(sh.Columns(3)), 14)
[/vba]

Автор - _Boroda_
Дата добавления - 18.03.2016 в 19:49
Callione Дата: Понедельник, 21.03.2016, 10:47 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Добрый день,
спасибо за ваши предложения, но оба предложенных способов не работают должным образом:

SLAVICK,
результатом кода стало вставка только одной пустой строки между данными с разных отчетов (у меня ранее вставлялись все имющиеся пустые строки с формулами).

_Boroda_,
результат вообще отсутствует, во второй файл ничего не переносится.


Сообщение отредактировал Callione - Понедельник, 21.03.2016, 10:48
 
Ответить
СообщениеДобрый день,
спасибо за ваши предложения, но оба предложенных способов не работают должным образом:

SLAVICK,
результатом кода стало вставка только одной пустой строки между данными с разных отчетов (у меня ранее вставлялись все имющиеся пустые строки с формулами).

_Boroda_,
результат вообще отсутствует, во второй файл ничего не переносится.

Автор - Callione
Дата добавления - 21.03.2016 в 10:47
SLAVICK Дата: Понедельник, 21.03.2016, 10:54 | Сообщение № 5
Группа: Модераторы
Ранг: Старожил
Сообщений: 1841
Репутация: 614 ±
Замечаний: 0% ±

2007,2010,2013,2016
результатом кода стало вставка одной пустой строки между данными

Ну так уменьшите на 1:
[vba]
Код
n = Application.WorksheetFunction.Match("", Columns("a"), 0)-1
[/vba]


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
результатом кода стало вставка одной пустой строки между данными

Ну так уменьшите на 1:
[vba]
Код
n = Application.WorksheetFunction.Match("", Columns("a"), 0)-1
[/vba]

Автор - SLAVICK
Дата добавления - 21.03.2016 в 10:54
Callione Дата: Понедельник, 21.03.2016, 12:20 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Ну так уменьшите на 1

оно работает)
понимаю что костыль, но сейчас главное результат! Спасибо!
 
Ответить
Сообщение
Ну так уменьшите на 1

оно работает)
понимаю что костыль, но сейчас главное результат! Спасибо!

Автор - Callione
Дата добавления - 21.03.2016 в 12:20
Wasilich Дата: Понедельник, 21.03.2016, 14:15 | Сообщение № 7
Группа: Друзья
Ранг: Ветеран
Сообщений: 871
Репутация: 221 ±
Замечаний: 0% ±

2003
понимаю что костыль, но сейчас главное результат!
Что бы было без костылей, надо было на 2-х листах сделать разные отчеты как есть, а на третьем показать как надо. И результат мог быть без костылей. ИМХО.
 
Ответить
Сообщение
понимаю что костыль, но сейчас главное результат!
Что бы было без костылей, надо было на 2-х листах сделать разные отчеты как есть, а на третьем показать как надо. И результат мог быть без костылей. ИМХО.

Автор - Wasilich
Дата добавления - 21.03.2016 в 14:15
Callione Дата: Понедельник, 21.03.2016, 15:25 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
SLAVICK, обнаружил, что предложенное вами решение не работает при обработке отчета в 37 тыс строк, оно просто игнорирует этот отчет..
Если отключаю ваши две строчки и включаю старую - все работает ок и этот проблемный отчет (в т.ч.) тоже копируется во второй файл.
Не знаете, почему такое происходит?

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

Wasilich, видимо так и произойдет, если на пальцах (в теории) не найдем понимания. Структура отчетов очень сложная, чтобы подготовить рабочий шаблон для размещения тут в примерах, уйдет минут 40, нет сейчас столько времени, извините.
[moder]Ну да, правильно, зачем Вам свое время тратить, если можно тратить наше - вдруг кто-нибудь да угадает?


Сообщение отредактировал _Boroda_ - Понедельник, 21.03.2016, 15:36
 
Ответить
СообщениеSLAVICK, обнаружил, что предложенное вами решение не работает при обработке отчета в 37 тыс строк, оно просто игнорирует этот отчет..
Если отключаю ваши две строчки и включаю старую - все работает ок и этот проблемный отчет (в т.ч.) тоже копируется во второй файл.
Не знаете, почему такое происходит?

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

Wasilich, видимо так и произойдет, если на пальцах (в теории) не найдем понимания. Структура отчетов очень сложная, чтобы подготовить рабочий шаблон для размещения тут в примерах, уйдет минут 40, нет сейчас столько времени, извините.
[moder]Ну да, правильно, зачем Вам свое время тратить, если можно тратить наше - вдруг кто-нибудь да угадает?

Автор - Callione
Дата добавления - 21.03.2016 в 15:25
SLAVICK Дата: Понедельник, 21.03.2016, 16:37 | Сообщение № 9
Группа: Модераторы
Ранг: Старожил
Сообщений: 1841
Репутация: 614 ±
Замечаний: 0% ±

2007,2010,2013,2016
Саш тут и угадывать не нужно.
На лицо лимит WorksheetFunction

Callione, - Wasilich прав - нужно сразу готовить правильные примеры (или говорить об объемах)- откуда мы знаем сколько и каких у Вас там данных?

Вот можно так:
[vba]
Код
n = sh.Evaluate("MATCH("""", a:a, 0)") - 1
[/vba]

или так:
[vba]
Код
n = Application.Match("", Columns("a"), 0) - 1
[/vba]

Или вообще через анализ массива - так больше код, но легче и удобнее корректировать. Можно быстро менять условия...:
[vba]
Код
Function lastRow(r As Range)
Dim m()
m = r.Value
    For n = UBound(m) To 1 Step -1
    If Len(m(n, 1)) > 0 Then Exit For
    Next
lastRow = n
End Function
[/vba]

Похоже не только в этом проблема на файле из 1- го поста работает только 3-й вариант. :o , хотя мой пример на данных того листа...
Добавлено
Понял в чем проблема 1-2-го варианта на примере файла не было формулы возвращающей "".
Используйте 3-й вариант он надежней - хоть и больше.
К сообщению приложен файл: All_data-3-.zip(74Kb)


Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Понедельник, 21.03.2016, 17:13
 
Ответить
СообщениеСаш тут и угадывать не нужно.
На лицо лимит WorksheetFunction

Callione, - Wasilich прав - нужно сразу готовить правильные примеры (или говорить об объемах)- откуда мы знаем сколько и каких у Вас там данных?

Вот можно так:
[vba]
Код
n = sh.Evaluate("MATCH("""", a:a, 0)") - 1
[/vba]

или так:
[vba]
Код
n = Application.Match("", Columns("a"), 0) - 1
[/vba]

Или вообще через анализ массива - так больше код, но легче и удобнее корректировать. Можно быстро менять условия...:
[vba]
Код
Function lastRow(r As Range)
Dim m()
m = r.Value
    For n = UBound(m) To 1 Step -1
    If Len(m(n, 1)) > 0 Then Exit For
    Next
lastRow = n
End Function
[/vba]

Похоже не только в этом проблема на файле из 1- го поста работает только 3-й вариант. :o , хотя мой пример на данных того листа...
Добавлено
Понял в чем проблема 1-2-го варианта на примере файла не было формулы возвращающей "".
Используйте 3-й вариант он надежней - хоть и больше.

Автор - SLAVICK
Дата добавления - 21.03.2016 в 16:37
Callione Дата: Понедельник, 21.03.2016, 19:40 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
SLAVICK, Wasilich, Вы безусловно правы, прошу меня извинить. Вот уж точно век живи и век учись - я даже и представить не мог, что у функции может существовать подобный лимит.

SLAVICK, подставил третий вариант, даже ничего подгонять не пришлось - все заработало с первого раза! В том числе и тот самый отчет :)
Спасибо, что нашли возможность помочь!
 
Ответить
СообщениеSLAVICK, Wasilich, Вы безусловно правы, прошу меня извинить. Вот уж точно век живи и век учись - я даже и представить не мог, что у функции может существовать подобный лимит.

SLAVICK, подставил третий вариант, даже ничего подгонять не пришлось - все заработало с первого раза! В том числе и тот самый отчет :)
Спасибо, что нашли возможность помочь!

Автор - Callione
Дата добавления - 21.03.2016 в 19:40
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Макрос копирует пустые строки при переносе данных в Excel (Макросы/Sub)
Страница 1 из 11
Поиск:

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