Автоматическое определение даипазона в VBA (динамический диапазон)

[Gurza]

Всем доброго времени суток.

Закончил колодвать над одной талицей, но начальству захотелось, чтобы все решалось нажатеим нескольких кнопок. Поэтому пришлось ортатиться к макросам, а так как я не силен в их написании (от слова совсем), то для меня остается только опция - ЗАПИСАТЬ МАКРОС и дальше уже колдовать в нем.

Собственно создал один макрос. Но возникла проблема с динамическим диапазоном данных в таблице из которой подтягиваются данные для отчета (таблица данных).

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

Собственно сам макрос.

Sub DayAndRoss_DataTransfer()
        Sheets("D&R Website Manifest").Select
    Range("L47").Select
    Cells.Find(What:="Resource id #14", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Selection.ClearContents
    Range("E12").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$W$199"), , xlYes).Name = "Table7"
    Range("Table7[#All]").Select
    ActiveSheet.ListObjects("Table7").TableStyle = "TableStyleLight1"
    Range("Table7[Probill '#]").Select
    Selection.Copy
    Sheets("D&R IN TRANSIT").Select
    Range("Table2[Probill '#]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("H2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IFERROR(IF(INDEX('D&R Website Manifest'!R2C2:R199C24,MATCH(RC9,'D&R Website Manifest'!R2C1:R199C1,0),MATCH(R1C,'D&R Website Manifest'!R1C1:R1C24,0)-1)=0,"""",INDEX('D&R Website Manifest'!R2C2:R199C24,MATCH(RC9,'D&R Website Manifest'!R2C1:R199C1,0),MATCH(R1C,'D&R Website Manifest'!R1C1:R1C24,0)-1)),"""")"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("A2:H2"), Type:=xlFillDefault
    Range("A2:H2").Select
    Range("A2:G2").Select
    Selection.AutoFill Destination:=Range("Table2[[Ship Date]:[Consignee Address 1]]")
    Range("Table2[[Ship Date]:[Consignee Address 1]]").Select
    Range("Table2[[Pieces]:[PO '#]]").Select
    Selection.NumberFormat = "General"
End Sub

_____
Код программы нужно выделять (форматировать) тегами [CODE] (читать FAQ)
Модератор


Проблема именно в диапозоне выделенным красном. В данном макросе я специально поставил такой диапозон, чтобы не прогадать.

Необходимо, чтобы при запуске макроса, программа автоматически определяла диапозон, исходя из количества ячеек имеющих данные, в таблице данных.

[alex77755]

первое, что бросилось в глаза: море лишних действий:

Range("L47").Select
Range("E12").Select
Range("Table7[#All]").Select
Range("Table7[Probill '#]").Select
Sheets("D&R IN TRANSIT").Select
Range("Table2[Probill '#]").Select
Range("H2").Select
Range("H2").Select
Range("A2:H2").Select
Range("A2:G2").Select
Range("Table2[[Ship Date]:[Consignee Address 1]]").Select
Range("Table2[[Pieces]:[PO '#]]").Select

[alex77755]

а вот количество строк будет меняться

для определения занятых строк имеется море способов:

lLastRow = Cells(Rows.Count,1).End(xlUp).Row
lLastRow = Cells.SpecialCells(xlLastCell).Row
lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
LastRow = Worksheets(1).Cells(1, 1).CurrentRegion.Rows.Count

[Gurza]

Сообщение от alex77755

первое, что бросилось в глаза: море лишних действий:

Я понимаю, что много мусора. Но у меня он неизбежен, так как я только начинаю что-то делать в VBA. Пожтому и пользуюсь функцией записи макроса, а она пишет все подряд.
Я конечно чищу потом, но моих познаний не хватает чтобы качественно почистить(((

Сообщение от alex77755

для определения занятых строк имеется море способов:

lLastRow = Cells(Rows.Count,1).End(xlUp).Row
lLastRow = Cells.SpecialCells(xlLastCell).Row
lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
LastRow = Worksheets(1).Cells(1, 1).CurrentRegion.Rows.Count

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

[Hugo121]

Без файла не сказать что лучше применить. Попробую угадать - может Range("$A$1").currentregion?

[Gurza]

Сообщение от Hugo121

Без файла не сказать что лучше применить. Попробую угадать - может Range("$A$1").currentregion?

Вы обсалютно правы.

Пришлось потратить часик-два, но приобретные знания стоят гораздо больше

Сейчас это выглядит так:

Sub
Sheets("D&R Website Manifest").Select
Range("A1").CurrentRegion.Name = "WorkArea"
ActiveSheet.ListObjects.Add(xlSrcRa nge, Range("WorkArea"), , xlYes).Name = "Table5"
Range("Table5[#All]").Select
ActiveSheet.ListObjects("Table5").T ableStyle = "TableStyleLight1"
End Sub

Спасибо всем кто откликнулся!

[Hugo121]

Возможно (да нет, точно ) красивее так:

    With Sheets("D&R Website Manifest")
        .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "Table5"
        .ListObjects("Table5").TableStyle = "TableStyleLight1"
    End With

[Gurza]

Сообщение от Hugo121

Возможно (да нет, точно ) красивее так:

    With Sheets("D&R Website Manifest")
        .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "Table5"
        .ListObjects("Table5").TableStyle = "TableStyleLight1"
    End With

Полностью согласен.

Но моих познаний в VBA пока не хватате понять - почему он ругается, когда я заменяю на предложенный Вами вариант.

Продолжу ковыряться

[Hugo121]

Я мало работал с этими листобъектами - может всёж ему (который ругается) нужно чтоб лист был активным... Без файла не проверить, писал вслепую.

[Gurza]

Сообщение от Hugo121

Я мало работал с этими листобъектами - может всёж ему (который ругается) нужно чтоб лист был активным... Без файла не проверить, писал вслепую.

Может быть)
Помыкаюсь пока сам и если не получится, тогда выложу пример сюда - может кому пригодиться)))