Tworząc makra, które mają działać na zmiennym lub różnych plikach podstawowym problemem jaki napotykamy jest identyfikacja komórek, w których znajdują się dane. Innymi słowy: jak znaleźć ilość wierszy i kolumn określających zakres danych do przetworzenia.

Zakres o zmiennej wielkości

Arkusz o strukturze podobnej jak na poniższym rysunku jest dość często spotykanym raportem, którego cechą charakterystyczną jest to, że co miesiąc (lub inny okres czasu) dopisywana jest linia z danymi. Tworząc makro nie wiemy jaki będzie zakres danych w momencie uruchamiania makra - musimy tak je skonstruować, aby makro działało niezależnie od wielkości zakresu.

Proponuję użyć do tego właściwość CurrentRegion obiektu Range, jak w poniższym przykładzie:

 
Sub ZaznaczBiezacyRegion()
 
    Range("A1").CurrentRegion.Select
 
End Sub
 

To makro zaznaczy obszar wypełniony danymi przylegający do komórki A1. Jak łatwo się domyślić, korzystając z tej metody musimy znać adres przynajmniej jednej komórki znajdującej się w obrębie danych, przy czym nie musi to być żadna konkretna komórka - może być dowolna leżąca wewnątrz lub na granicy poszukiwanego adresu.

Pamiętajcie też, że do operacji na zakresach z poziomu VBA nie musicie wcześniej zaznaczać zakresu.

Poszukiwanie granic zakresów

Jeśli z jakichś powodów nie chcemy określać całego bieżącego zakresu danych lecz tylko określić granice tego zakresu (począwszy od jakiejś, np. bieżącej komórki) możemy użyć metody End obiektu Range. Działanie jest podobne np. do naciśnięcia klawiszy Ctrl+Shift+→ - wciskając tę kombinację zaznaczymy komórki aż do pierwszej pustej po prawej stronie. Użycie tej metody jest następujące:

 
Sub ZaznaczDoDolu()
 
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
 
End Sub
 

To, w jakim kierunku zaznaczenie będzie szukać granic zależy od argumentu metody End. Oprócz użytego w naszym przykładzie xlDown możemy użyć wszystkich kierunków: xlUp, xlToLeft, xlToRight, xlDown.

Uwaga! Jeśli w obrębie zakresu znajdują się puste komórki powyższa metoda może nie dać oczekiwanych wyników. Przy rozwiązywaniu rzeczywistych problemów należy testować, myśleć, wymyślać nowe rozwiązania...

Znajdowanie następnego pustego wiersza lub kolumny

Kolejnym problemem, związanym z identyfikacją zakresów sytuacja, kiedy makro uzupełnia okresowo dany raport poprzez dopisywanie danych na końcu. Musimy wtedy odnaleźć kolejny pusty wiersz lub kolumnę. Najlepiej nadaje się do tego opisana powyżej metoda End obiektu range. Przykładowy sposób znajdowania pierwszego wolnego (pustego) wiersza może wyglądać następująco:

 
Sub ZnajdzPierwszyPustyWiersz()
 
Dim NextRow As Long
 
    NextRow = Range("A65536").End(xlUp).Row + 1
 
End Sub
 

Zastosowaliśmy tutaj pewien trick: zamiast szukać od góry szukamy od dołu (zakładając, że pracujemy w Excelu 2003, bowiem dla Excela 2007 poszukiwanie powinno się zacząć od komórki A1048576). Zyskujemy dzięki temu pewność, że znajdziemy wiersz wolny nawet wtedy, gdy w zakresie danych (kolumny A) znajdują się jakieś przypadkowe puste komórki. Oczywiście można sobie wyobrazić sytuację, że w rzeczywistych sytuacjach lepiej będzie szukać po kolumnie B lub C i nie od dołu lecz od góry, ale to już zależy od konfiguracji danych, z jakimi przyjdzie Wam pracować. Jak zwykle, to jakiego sposobu identyfikacji granicy zakresu użyjemy, zależy od danej sytuacji, z jaką przyjdzie nam się zmierzyć.

Na końcu tej instrukcji musimy dodać 1 ponieważ ta instrukcja znajduje granicę zakresu, zatem wolny wiersz będzie się znajdował niżej.

Analogicznie znajdziemy następną wolną kolumnę:

 
Sub ZnajdzPierwszaPustaKolumne()
 
Dim NextColumn As Integer
 
    NextColumn = Range("IV1").End(xlToLeft).Column + 1
 
End Sub
 

Dla Excela 2007 powinniśmy użyć komórki startowej XFD1.

One Response to “Wtorki z VBA cz. 8 – Identyfikacja zakresów”

1. Wtorki z VBA cz. 9 – Wprowadzanie wartości do arkusza says:

   December 14, 2009 at 10:06 pm

   [...] Podane wyżej przykłady są bardzo proste; przede wszystkim wpisują wartości na "sztywno" do tej samej komórki (A1), nie sprawdzają poprawności danych ani nie konwertują łańcucha zwracanego przez funkcję InputBox na liczby (w przypadku jeśli to konieczne). Proponuję małe ćwiczenie: napisanie procedury, która pobierze dane od użytkownika (np. jego wiek), przekształci je w liczbę oraz wpisze do następnego wolnego wiersza w arkuszu. Wskazówki możecie znaleźć w poście: Wtorki z VBA cz. 8 – Identyfikacja zakresów. [...]