Определения размера диапазона с переменным количеством строк

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Определения размера диапазона с переменным количеством строк (Макросы/Sub)

Karlson7

Дата: Вторник, 23.02.2021, 02:18 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

Уважаемые форумчане, требуется помощь.
Если вкратце: есть таблица, первая строка - заголовки колонок (всего 10) и первая строка таблицы — формулы, которые берут данные из другого файла (магазин наличие каталог.xls) и обрабатывают их.
Мне надо скопировать строку с формулами вниз по листу на n-строк. Количество строк задается размером таблицы в файле "магазин наличие каталог.xls". Количество этих строк меняется каждый день, обычно их там около 10000 строк.
И вот определить макросом размер диапазона, откуда берутся данные, никак не получается. Хочу максимально автоматизировать процесс, но не знаю, как протянуть первую строку с формулами на нужное количество строк так, чтобы получить все данные из файла "магазин наличие каталог.xls", так как в основном использую макрорекордер. И в этом случае у меня получается конкретный фиксированный диапазон, но, каждый день число строк меняется
При этом я могу посчитать количество строк в Файле "магазин наличие каталог.xls".

То есть в макросе мне надо как-то определить диапазон, куда вставить формулы.
У меня есть начало диапазона A2, формулой СЧЁТЗ посчитал число строк, и есть последняя колонка К
В конкретном случае диапазон A2:K8569. Но число строк все время меняется.
Можно написать макрос, который на основании таких данных определит диапазон, куда надо вставлять формулы?
Или просто скопирует данные за нужное число строк, определенное по СЧЁТЗ
Заранее всем спасибо за ответы!
Код макроса, который у меня получился с фиксированным размером диапазона (в основном сделал макрорекордером)
[vba]

Код

Sub Каталог_наличие_убрать_перенос_текста()
'
' Макрос5 Макрос
'

'
    Workbooks.Open Filename:= _
        "C:\Works_GD\Интернет магазин\для форума\магазин наличие каталог.xls", UpdateLinks:=3
        Sheets("Лист1").Select 'очистка листа от данных
        Cells.Select
    Selection.ClearContents
    Sheets("Формулы").Select
    Rows("1:2").Select 'копирование заголовков столбцов и первой строки с формулами
    Selection.Copy
    Sheets("Лист1").Select
    Range("A1").Select
    ActiveSheet.Paste 'копирование заголовок столбцов и формул для обработки данных
    Range("A2:K2").Select
    Application.CutCopyMode = False 'копирование формул на листе
    Selection.Copy
    Range("A2:K9488").Select
    ActiveSheet.Paste
    Range("A1:K9488").Select
    Range("A2").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

[/vba]
В макросе просто вручную прописал диапазон куда вставляются формулы, а хочется это сделать автоматически, исходя из числа строк в файле с данными
Файл Каталог_наличие_работа.xls — рабочий файл, где находятся формулы и код макроса
В приложенных файлах я сократил количество строк, чтобы уменьшить их размер.

Код

К сообщению приложен файл: __.xls (87.0 Kb) · 2536164.xls (183.5 Kb)

Ответить

СообщениеУважаемые форумчане, требуется помощь.
Если вкратце: есть таблица, первая строка - заголовки колонок (всего 10) и первая строка таблицы — формулы, которые берут данные из другого файла (магазин наличие каталог.xls) и обрабатывают их.
Мне надо скопировать строку с формулами вниз по листу на n-строк. Количество строк задается размером таблицы в файле "магазин наличие каталог.xls". Количество этих строк меняется каждый день, обычно их там около 10000 строк.
И вот определить макросом размер диапазона, откуда берутся данные, никак не получается. Хочу максимально автоматизировать процесс, но не знаю, как протянуть первую строку с формулами на нужное количество строк так, чтобы получить все данные из файла "магазин наличие каталог.xls", так как в основном использую макрорекордер. И в этом случае у меня получается конкретный фиксированный диапазон, но, каждый день число строк меняется
При этом я могу посчитать количество строк в Файле "магазин наличие каталог.xls".

То есть в макросе мне надо как-то определить диапазон, куда вставить формулы.
У меня есть начало диапазона A2, формулой СЧЁТЗ посчитал число строк, и есть последняя колонка К
В конкретном случае диапазон A2:K8569. Но число строк все время меняется.
Можно написать макрос, который на основании таких данных определит диапазон, куда надо вставлять формулы?
Или просто скопирует данные за нужное число строк, определенное по СЧЁТЗ
Заранее всем спасибо за ответы!
Код макроса, который у меня получился с фиксированным размером диапазона (в основном сделал макрорекордером)
[vba]

Код

Автор - Karlson7
Дата добавления - 23.02.2021 в 02:18

probkinfeda

Дата: Вторник, 23.02.2021, 08:17 | Сообщение № 2

Группа: Пользователи

Ранг: Новичок

Сообщений: 25

Репутация: 5 ±

Замечаний: 0% ±

Дорогой Karlson7
В вашем программном коде вы 3 раза копируете и вставляете данные причем в одной книге <магазин наличие каталог.xls>
Вы столько много написали а толку мало Учитесь правильно ставить задачу чтобы вашему собеседнику было понятно.
Насколько я понял надо диапазон данных из одной книги скопировать в другую так или нет

Ответить

Karlson7

Дата: Вторник, 23.02.2021, 10:33 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

Цитата probkinfeda, 23.02.2021 в 08:17, в сообщении № 2 ()

В вашем программном коде вы 3 раза копируете и вставляете данные причем в одной книге <магазин наличие каталог.xls>
Вы столько много написали а толку мало Учитесь правильно ставить задачу чтобы вашему собеседнику было понятно.
Насколько я понял надо диапазон данных из одной книги скопировать в другую так или нет

probkinfeda,

Извините. да, вручную правил код перед вставкой в сообщение и напутал.
Есть файл с данными "магазин наличие каталог.xls", он выгружается из старой складской программы и там в одной колонке в тексе есть невидимые символы перехода строки.
При обновление интернет магазина это дает ошибку. Я могу с помощью формулы ПЕЧСИМВ убрать эти символы.

Макрос работает так: он из рабочего файла открывает файл "Каталог_наличие_работа.xls", в котором стоят формулы, обрабатывающие данные из складской программы, обновляет линки на файлы. Формулы стоят только в одной первой строке таблицы. Протягивает эти формулы вниз, чтобы получить исправленные данные из файла "магазин наличие каталог.xls"
Затем эти данные вставляются на другой лист файла "Каталог_наличие_работа.xls" как значения, так как на этом листе будет дальнейшая обработка данных.
Проблема в том, что я не умею программным путем определить, насколько надо протягивать строки с формулой ПЕЧСИМВ.
Когда я сделал макрос макрорекордером, он скопировал строки с формулой настолько, на сколько я их протянул мышкой, а количество строк с корявыми данными меняется каждый раз.
Поэтому хочется, чтобы макрос определил число строк в таблице с данными в файле "магазин наличие каталог.xls", и протянул строку с формулами в файле "Каталог_наличие_работа.xls" именно на это число строк.
Правильный код выглядит так
[vba]

Код

Sub Каталог_наличие_убрать_перенос_текста()
'
' Макрос5 Макрос
'

'
    Workbooks.Open Filename:= _
        "C:\Works_GD\Интернет магазин\для форума\Каталог_наличие_работа.xls", UpdateLinks:=3
        Sheets("Лист1").Select 'очистка листа от данных
        Cells.Select
    Selection.ClearContents
    Sheets("Формулы").Select
    Rows("1:2").Select 'копирование заголовков столбцов и первой строки с формулами
    Selection.Copy
    Sheets("Лист1").Select
    Range("A1").Select
    ActiveSheet.Paste 'копирование заголовок столбцов и формул для обработки данных
    Range("A2:K2").Select
    Application.CutCopyMode = False 'копирование формул на листе
    Selection.Copy
    Range("A2:K9488").Select
    ActiveSheet.Paste
    Range("A1:K9488").Select
    Range("A2").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

[/vba]

Цитата probkinfeda, 23.02.2021 в 08:17, в сообщении № 2 ()

Код

[/vba]Karlson7

Ответить

Сообщение

Цитата probkinfeda, 23.02.2021 в 08:17, в сообщении № 2 ()

Код

[/vba]

Автор - Karlson7
Дата добавления - 23.02.2021 в 10:33

probkinfeda

Дата: Вторник, 23.02.2021, 11:08 | Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 25

Репутация: 5 ±

Замечаний: 0% ±

Если в файле "магазин наличие каталог.xls" таблица с данными неразрывная те в этом диапазоне нет пустых строк и столбцов
тогда эти операторы помогут
[vba]

Код

Sub fdsgh()
Dim Rg1 As Range, i1&, iK&
Set Rg1 = Workbooks("Имя книги").Worksheets("Имя листа").Cells(2, 2).CurrentRegion
i1 = Rg1.Row 'номер 1 строки диапазона
iK = Rg1.Rows.Count ' Количество строк в диапазоне
adr = Rg1.Cells(Rg1.Cells.Count).Address ' адрес последней ячейки диапазона
kStr = i1 + iK - 1 'Номер последней строки диапазона
End Sub

[/vba]
Cells(2,2) это первая ячейка вашего диапазона данных у вас она другая. те замените цифры первая номер строки вторая номер столбца.

Ответить

Pelena

Дата: Вторник, 23.02.2021, 11:17 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 19174

Репутация: 4413 ±

Замечаний: ±

Excel 365 & Mac Excel

Karlson7, а может, проще сделать? Скопировать всю таблицу целиком и тем же макросом убрать символы перевода строки в столбце К?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Karlson7

Дата: Вторник, 23.02.2021, 16:23 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

probkinfeda,
Спасибо! Буду пробовать

Ответить

Karlson7

Дата: Вторник, 23.02.2021, 16:26 | Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

Цитата Pelena, 23.02.2021 в 11:17, в сообщении № 5 ()

а может, проще сделать? Скопировать всю таблицу целиком и тем же макросом убрать символы перевода строки в столбце К?

Дело в том, что сейчас это делается пару раз в неделю, а надо делать каждый день, и там еще дальнейшая обработка идет. Поэтому желательно максимально автоматизировать процесс

Ответить

Страница 1 из 1
1