Гость
Целевая тема:
Создать новую тему:
Автор:
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Номер последней строки или столбца / 25 сообщений из 51, страница 1 из 3
14.02.2007, 08:48
    #34329000
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
Недавно начал пробовать решать небольшие задачки с помощью макросов и часто сталкиваюсь с похожей задачей, когда нужно найти последнюю строку содержащюю данные, чтобы запустить цикл или скопировать определённую область. Идя по пути наименьшего сопротивления я в этих случаях всегда ищу столбец или строчку, в которой предполагаю не будет пустых ячеек, и подсчитываю количество простой функцией рабочего листа.

Код: plaintext
       LastRow = WorksheetFunction.CountIf(Range(Cells( 1 , x), Cells( 65536 , x)), "*?")

Но всё время есть опасность что в исходных данных будет где нибуть пустая ячейка (возможно просто ошибка) и последняя строчка посчитается неправильно. Да и вообще как найти последнюю строку или столбец диапазона с данными если если не знаешь будут ли там пустые ячейки встречаться или нет? Надеюсь у меня получилось объяснить.
...
Рейтинг: 0 / 0
14.02.2007, 10:30
    #34329272
vbapro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
посмотри этот топик
http://www.sql.ru/forum/actualthread.aspx?tid=385309
...
Рейтинг: 0 / 0
14.02.2007, 11:47
    #34329611
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
Function LastCell(ws As Worksheet) As Range
  Dim LastRow&, LastCol%


   'From Rodney Powell, Microsoft MVP
   '"The sample procedure below is a variation on a
   'technique I learned from fellow Excel MVP, Bob Umlas."
   '
   'http://www.exceldeveloper.com/geeks012.shtml


  On Error Resume Next
  With ws
  ' Find the last real row
    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row
  ' Find the last real column
    LastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
  End With


' Initialize a Range object variable
' for the last populated row.
  Set LastCell = ws.Cells(LastRow&, LastCol%)
   
End Function

Sub test1()
   
x = LastCell(Sheets("ÏÔ_Çàïàñû")).Address

   
   Debug.Print x

End Sub

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Sub test2()
    Dim rng As Range
        
    Set rng = Cells
    
    x = Intersect( _
      rng.Find("*", rng( 1 ), xlValues, xlWhole, xlByRows, _
        xlPrevious).EntireRow, _
      rng.Find("*", rng( 1 ), xlValues, xlWhole, xlByColumns, _
        xlPrevious).EntireColumn).Address
    
    Debug.Print x
    
End Sub
 


Извиняюсь за вопросы, но если можно то я задам, а отвечать или нет вам решать
1) Я понимаю что вышеописанные варианты оба почти одно и то же, но может есть какая-то разница, если есть, то подскажите
2)Что значит
Код: plaintext
Set rng = Cells
3)что такое
Код: plaintext
rng( 1 )
во втором примере
4) И самое главное - возможно ли не считать те ячейки которые содержат значения ошибки или "0" (это в том случае если на листе формулы протянуты дальше чем заканчивается диапазон данных)
...
Рейтинг: 0 / 0
14.02.2007, 11:59
    #34329681
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
Извиняюсь опять же, сасое главное забыл вышеописанные способы лучше чем приведённый далее?
Если да, то почему?

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
 Sub test3()
     
     Dim i As Long
    x = ActiveWorkbook.ActiveSheet.Cells(Rows.Count,  1 ).End(xlUp).Row
           
    Debug.Print x
    
End Sub
...
Рейтинг: 0 / 0
14.02.2007, 12:01
    #34329696
vbapro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
1) первый вариант может быть использован для разных листов, т.к. лист указан в аргументе. Более универсальный вариант и, отличие от второго, не требует доработки для использования. Второй вариант - только идея.

2) Set rng = Cells - в переменную rng помещается диапазон "все ячейки текущего листа".

3) rng(1) - ссылка на первую ячейку по порядку из тех, что представлены в этом объекте (нумерация идет сначала в первом ряде слева направо, потом во втором и т.д.)

4) можно, но нужно дорабатывать, легче всего будет для конкретного листа с данными
...
Рейтинг: 0 / 0
14.02.2007, 12:02
    #34329701
vbapro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
DeggasadИзвиняюсь опять же, сасое главное забыл вышеописанные способы лучше чем приведённый далее?
Если да, то почему?

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
 Sub test3()
     
     Dim i As Long
    x = ActiveWorkbook.ActiveSheet.Cells(Rows.Count,  1 ).End(xlUp).Row
           
    Debug.Print x
    
End Sub
читай сообщения топика, откуда взял
...
Рейтинг: 0 / 0
14.02.2007, 13:11
    #34330062
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
vbapro1) первый вариант может быть использован для разных листов, т.к. лист указан в аргументе. Более универсальный вариант и, отличие от второго, не требует доработки для использования. Второй вариант - только идея.

2) Set rng = Cells - в переменную rng помещается диапазон "все ячейки текущего листа".

3) rng(1) - ссылка на первую ячейку по порядку из тех, что представлены в этом объекте (нумерация идет сначала в первом ряде слева направо, потом во втором и т.д.)

4) можно, но нужно дорабатывать, легче всего будет для конкретного листа с данными

1) Не понятно какая доработка нужна 2-му способу?
4) А как доработать?

vbapro Deggasad
Извиняюсь опять же, сасое главное забыл вышеописанные способы лучше чем приведённый далее?
Если да, то почему?


Код: plaintext
1.
2.
3.
4.
5.
 Sub test3()
     
     Dim i As Long
    x = ActiveWorkbook.ActiveSheet.Cells(Rows.Count,  1 ).End(xlUp).Row
           
    Debug.Print x

End Sub

читай сообщения топика, откуда взял

Читал, так и не понял если честно отличий там сказано, что это для листа целиком, так и 2 предыдущих примера вроде для листа целиком

З.Ы.: Если задаю неправильные вопросы, не ругайте, просто я лучше их задам и не получу ответа, чем не попытаюсь мпросить совсем, это не значит что я совсем сам не хочу думать, на 80% вопросов которые у меня возникают я отвечаю сам. Спасибо!
...
Рейтинг: 0 / 0
14.02.2007, 13:46
    #34330233
KL (XL)
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
DeggasadИзвиняюсь опять же, сасое главное забыл вышеописанные способы лучше чем приведённый далее?
Если да, то почему?

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
 Sub test3()
     
     Dim i As Long
    x = ActiveWorkbook.ActiveSheet.Cells(Rows.Count,  1 ).End(xlUp).Row
           
    Debug.Print x
    
End Sub


Здесь речь не может идти о "лучше - хуже" - данные коды выполняют разные функции:

- первые два кода (по сути одно и то же, только один - Function, а др. Sub) ищут последнюю ячейку по всему листу (во всех столбцах)

- вышеуказанный код ищет последнюю ячейку в одном конкретном столбце

KL
[MVP - Microsoft Excel]
...
Рейтинг: 0 / 0
14.02.2007, 13:52
    #34330276
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
KL (XL) DeggasadИзвиняюсь опять же, сасое главное забыл вышеописанные способы лучше чем приведённый далее?
Если да, то почему?

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
 Sub test3()
     
     Dim i As Long
    x = ActiveWorkbook.ActiveSheet.Cells(Rows.Count,  1 ).End(xlUp).Row
           
    Debug.Print x
    
End Sub


Здесь речь не может идти о "лучше - хуже" - данные коды выполняют разные функции:

- первые два кода (по сути одно и то же, только один - Function, а др. Sub) ищут последнюю ячейку по всему листу (во всех столбцах)

- вышеуказанный код ищет последнюю ячейку в одном конкретном столбце



KL
[MVP - Microsoft Excel]

Насчёт этого понял. Спасибо

Открытый вопрос - Как не считать те ячейки которые содержат значения ошибки или "0" (это в том случае если на листе формулы протянуты дальше чем заканчивается диапазон данных)?
...
Рейтинг: 0 / 0
14.02.2007, 13:56
    #34330301
KL (XL)
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
Deggasad
1) Не понятно какая доработка нужна 2-му способу?
4) А как доработать?
1) например - перехват ошибок и др.
4) без конкретного примера трудно сказать

DeggasadЗ.Ы.: Если задаю неправильные вопросы, не ругайте, просто я лучше их задам и не получу ответа, чем не попытаюсь мпросить совсем, это не значит что я совсем сам не хочу думать, на 80% вопросов которые у меня возникают я отвечаю сам. Спасибо!
А что, кто-то тебя ругает? По-моему, vbapro очень корректно и на все отвечает :-)
...
Рейтинг: 0 / 0
14.02.2007, 14:26
    #34330436
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
KL (XL)
1) например - перехват ошибок и др.
4) без конкретного примера трудно сказать


Конкретный пример прилагаю

Попытался максимально приблизить к реальности и в модуль уже записал оба варианта
(и Function, и Sub) нахождения последней ячейки.

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

За корректные ответы спасибо большое, я ведь эту корректность и не пытался поставить под сомнение! Просто боюсь показаться полным лентяем, так сказать больше оправдание для самого себя почему столько вопросов.
...
Рейтинг: 0 / 0
14.02.2007, 14:42
    #34330507
vkodor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
Deggasad
Необходимо посчитать последнюю ячейку, не считая помеченных жёлтым цветом. Исхожу из условия что изменить формулы не имею возможности.

Обычно такой проблемы не стоит, т.к. на практике всегда есть уникальный столбец без формул по которому и определяют последнюю запись.
Код: plaintext
x = ActiveWorkbook.ActiveSheet.Cells(Rows.Count, n).End(xlUp).Row
где n - номер солбца по которому необходимо определить последнюю запись
...
Рейтинг: 0 / 0
14.02.2007, 14:48
    #34330536
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
vkodor Deggasad
Необходимо посчитать последнюю ячейку, не считая помеченных жёлтым цветом. Исхожу из условия что изменить формулы не имею возможности.

Обычно такой проблемы не стоит, т.к. на практике всегда есть уникальный столбец без формул по которому и определяют последнюю запись.
Код: plaintext
x = ActiveWorkbook.ActiveSheet.Cells(Rows.Count, n).End(xlUp).Row
где n - номер солбца по которому необходимо определить последнюю запись

Я так сейчас и делаю (ну не совсем так, но теперь буду делать так)
Просто случай такой, что теоритически в любом столбце последняя строчка может быть пустой, хотя в другом будет какое-то значение, поэтому и хочу перестраховаться на будущее!
...
Рейтинг: 0 / 0
14.02.2007, 15:11
    #34330667
vbapro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
можно сделать комбинацию методик
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Function LastRow(Optional KeyColumn& =  0 , Optional WS As Worksheet) As Long
    
    If WS Is Nothing Then Set WS = ActiveSheet
    
    If KeyColumn =  0  Then
        LastRow = WS.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Else
        LastRow = WS.Cells(Rows.Count, KeyColumn).End(xlUp).Row
    End If
   
End Function
если нужно определить по колонке, что чаще всего и применяется, я согласен с vkodor , то указываешь колонку, не указываешь - то получаешь максимум. По аналогии с этой функцией можно сделать функцию и для определения последней колонки.

Далее по вопросам: проверку ячеек, что в них находится, лучше сделать в основном цикле. Т.е находишь физический конец данных с помощью универсальной функции LastRow, а в основной программе уже определяешь что там за данные: ошибки, #N/A или нули, и соотв. обрабатываешь
...
Рейтинг: 0 / 0
14.02.2007, 15:47
    #34330829
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
vbapro
Далее по вопросам: проверку ячеек, что в них находится, лучше сделать в основном цикле. Т.е находишь физический конец данных с помощью универсальной функции LastRow, а в основной программе уже определяешь что там за данные: ошибки, #N/A или нули, и соотв. обрабатываешь
Идею понял
...
Рейтинг: 0 / 0
14.02.2007, 15:57
    #34330885
vbapro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
vbapro
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Function LastRow(Optional KeyColumn& =  0 , Optional WS As Worksheet) As Long
    
    If WS Is Nothing Then Set WS = ActiveSheet
    
    If KeyColumn =  0  Then
        LastRow = WS.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Else
        LastRow = WS.Cells(Rows.Count, KeyColumn).End(xlUp).Row
    End If
   
End Function

ошибка: нужно WS.Rows.Count вместо Rows.Count
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Public Function LastRow(Optional KeyColumn& =  0 , Optional WS As Worksheet) As Long
    
    If WS Is Nothing Then Set WS = ActiveSheet
    
    If KeyColumn =  0  Then
        LastRow = WS.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Else
        LastRow = WS.Cells(WS.Rows.Count, KeyColumn).End(xlUp).Row
    End If
   
End Function
...
Рейтинг: 0 / 0
14.02.2007, 16:06
    #34330923
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
Как мог так и написал, по крайней мере надеюсь идея понятна, если подскажите как функции рабочего листа не использовать буду благодарен, сейчас работает впринципе нормально.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
Public Function LastRow(Optional KeyColumn& =  0 , Optional WS As Worksheet) As Long
    
    If WS Is Nothing Then Set WS = ActiveSheet
    
    If KeyColumn =  0  Then
        LastRow = WS.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
         Do While z =  0 
           z = WorksheetFunction.CountIf(Range(Cells(LastRow,  1 ), Cells(LastRow,  256 )), "*?") + _
               WorksheetFunction.CountIf(Range(Cells(LastRow,  1 ), Cells(LastRow,  256 )), ">0")
   
             If z =  0  Then
              LastRow = LastRow -  1 
             End If
         Loop
    Else
        LastRow = WS.Cells(WS.Rows.Count, KeyColumn).End(xlUp).Row
    End If
   
End Function
...
Рейтинг: 0 / 0
14.02.2007, 16:24
    #34331005
vbapro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
для твоего частного случая, может быть, это совсем неплохой вариант. Ну тогда, хотя бы раздели функционально на две части, чтоб LastRow можно было использовать в других местах:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
Public Function LastRow(Optional KeyColumn& =  0 , Optional WS As Worksheet) As Long
    
    If WS Is Nothing Then Set WS = ActiveSheet
    
    If KeyColumn =  0  Then
        LastRow = WS.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Else
        LastRow = WS.Cells(WS.Rows.Count, KeyColumn).End(xlUp).Row
    End If
   
End Function

Public Function LastRow_MySuper(Optional KeyColumn& =  0 , Optional WS As Worksheet) As Long
Dim z As Long

    LastRow_MySuper = LastRow(KeyColumn, WS)

    If KeyColumn =  0  Then
        LastRow_MySuper = LastRow_MySuper +  1 
        
        Do
            LastRow_MySuper = LastRow_MySuper -  1 
            
            z = WorksheetFunction.CountIf(Range(Cells(LastRow_MySuper,  1 ), Cells(LastRow_MySuper,  256 )), "*?") + _
                WorksheetFunction.CountIf(Range(Cells(LastRow_MySuper,  1 ), Cells(LastRow_MySuper,  256 )), ">0")
    
        Loop While z =  0 
    End If
    
End Function
...
Рейтинг: 0 / 0
14.02.2007, 16:54
    #34331134
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
Спасибо за участие
...
Рейтинг: 0 / 0
19.03.2007, 17:31
    #34400954
vbapro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
не вопрос, а факт.
т.к. сам участвовал в этом обсуждении и предлагал решения. решения оказались неверными, т.к. вот в таком файле не верно находится последняя колонка, если использовать алгоритм
Код: plaintext
1.
2.
3.
4.
? Intersect( _
      Cells.Find("*", Cells( 1 ), xlValues, xlWhole, xlByRows, _
        xlPrevious).EntireRow, _
      Cells.Find("*", Cells( 1 ), xlValues, xlWhole, xlByColumns, _
        xlPrevious).EntireColumn).Address
...
Рейтинг: 0 / 0
20.03.2007, 03:23
    #34401615
KL (XL)
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
vbaproне вопрос, а факт.
т.к. сам участвовал в этом обсуждении и предлагал решения. решения оказались неверными, т.к. вот в таком файле не верно находится последняя колонка, если использовать алгоритм
Код: plaintext
1.
2.
3.
4.
? Intersect( _
      Cells.Find("*", Cells( 1 ), xlValues, xlWhole, xlByRows, _
        xlPrevious).EntireRow, _
      Cells.Find("*", Cells( 1 ), xlValues, xlWhole, xlByColumns, _
        xlPrevious).EntireColumn).Address


Интересно, что данная ошибка возникает в Excel 2000-2007 и не возникает в Excel 97 :-) Я всегда говорил - слияние ячеек в Excel это едва ли не самая глючная фича :-(

KL
[MVP - Microsoft Excel]
...
Рейтинг: 0 / 0
14.09.2007, 21:18
    #34802214
vbapro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
Еще факт. Возможно я последний, кто с ним встретился :), но пусть будет в топике.
Конструкция типа
Код: plaintext
LastRow = Sh.Cells(Sh.Rows.Count,  1 ).End(xlUp).Row
дает ложный результат, если на листе включен автофильтр.
...
Рейтинг: 0 / 0
14.09.2007, 23:25
    #34802290
KL (XL)
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
vbaproЕще факт. Возможно я последний, кто с ним встретился :), но пусть будет в топике.
Конструкция типа
Код: plaintext
LastRow = Sh.Cells(Sh.Rows.Count,  1 ).End(xlUp).Row
дает ложный результат, если на листе включен автофильтр.

А пример выложить можешь и указать версию Excel? А то у меня no repro :-)

KL
[MVP - Microsoft Excel]
...
Рейтинг: 0 / 0
15.09.2007, 00:23
    #34802327
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
Пожалуй тоже поучаствую. Мой любимый find тупит наглухо с автофильтром.
В приложенном файле 2 листа. Если на первом косяк хоть как-то объясним (ищет последнюю видимую ячейку), то на втором листе вообще что-то непонятное, заметил, что это происходит когда все отфильтрованные значения находятся в начале списка и рядом друг с другом.
...
Рейтинг: 0 / 0
15.09.2007, 00:25
    #34802328
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Номер последней строки или столбца
vbaproЕще факт. Возможно я последний, кто с ним встретился :), но пусть будет в топике.
Конструкция типа
Код: plaintext
LastRow = Sh.Cells(Sh.Rows.Count,  1 ).End(xlUp).Row
дает ложный результат, если на листе включен автофильтр.

У меня всегда находит последнюю видимую, вроде логика есть.
...
Рейтинг: 0 / 0
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Номер последней строки или столбца / 25 сообщений из 51, страница 1 из 3
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]