VBA 2010 Selecteren data in kolom met lege cellen

[JKlerks]

Hallo,

ik ben met een macro een download dataset aan het transformeren naar een werkbaar document. Hiervoor selecteer ik cellen uit een bepaalde kolom en plak ik deze als waarde op een ander tabblad.

Hiervoor gebruik ik het volgende eenvoudige script:

Sheets("NIEUWE INVOER").Select
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("NIEUWE DATA").Select
Range("F4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Het gaat dus om alle data vanaf rij 2 naar beneden. In de basis zijn alle rijen die ik gebruik gevuld, waardoor de macro prima werkte.

Een van de datasets bevat nu tevens lege cellen. Dat houdt met bovenstaande code in dat alleen de eerst gevulde cel wordt gekopieerd en ik onjuiste data verwerk.

Als alternatief heb ik een andere kolom gebruikt om met ActiveCell.Offset(0, -3).Select naar de juiste kolom te gaan en van hier naar boven te selecteren. Bij het opnemen van de macro wordt de cel in de betreffende kolom uiteindelijk als waarde geselecteerd, waardoor het slechts eenmaal werkt.

Hoe kan ik van deze kolom alle waarden (dus inclusief de lege) in de kolom selecteren vanaf rij 2?

 

[roeljongman]

gaten in de reeks is inderdaad een probleem bij ddze selectiemethode maar Daar is een "simpel" trucje voor

in plaats vanaf B2 naar beneden te gaan voor selectie laat je de laatste cel bepalen door een cel met een hoog rijnummer te selecteren bijv B65000 en dan terug omhoog te gaan naar de laatst gevulde cel.

in jou macro ziet dat er zo uit.

Sheets("NIEUWE INVOER").Select
Range("B2").Select
Range(Selection, [COLOR="#FF0000"]Range("B65000").End(xlUp)[/COLOR]).Select
Selection.Copy
Sheets("NIEUWE DATA").Select
Range("F4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Jou macro is opgenomen code met de macro recorder en dat trucje moet je dus zelf handmatig invoeren want dat stuk opnemen is erg lastig.

 

[JKlerks]

Bedankt!

Het bepalen van een laatste waarde in alle kolommen en rijen lukt me wel, maar hier zat ik enorm te stoeien. Dit lijkt prima te werken

 

[edmoor]

Of:

ru = Sheets("NIEUWE INVOER").UsedRange.Rows.Count+1
Sheets("NIEUWE DATA").Range("F4:F" & ru+2) = Sheets("NIEUWE INVOER").Range("B2:B" & ru).Value

 

[JKlerks]

Of:

ru = Sheets("NIEUWE INVOER").UsedRange.Rows.Count+1
Sheets("NIEUWE DATA").Range("F4:F" & ru+2) = Sheets("NIEUWE INVOER").Range("B2:B" & ru).Value

Edmoor, dank voor je reactie!

Aangezien ik dit zelf niet zou bedenken en het graag begrijp.

Je formatteert de naam "ru", de count+1 gebruik je omdat ik in rij 2 begin, de ru+2 gebruik je omdat de doellocatie 2 rijen lager is en .value zorgt ervoor dat ik alleen de waarden overneem?

Ik ga er van uit dat het werkt en dat zou de macro enorm verkorten (hoewel het nu ook werkt), maar dan kan ik dat in de toekomst nog gebruiken

 

[roeljongman]

Bedankt!

Het bepalen van een laatste waarde in alle kolommen en rijen lukt me wel, maar hier zat ik enorm te stoeien. Dit lijkt prima te werken

het is een kwestie van even iets anders naar je data bereik kijken, dit was ook ooit voor mij een "eye opener"

Een volgende stap kan zijn om je opgenomen code op te schonen want de macro recorder neemt elke handeling op. maar excel code heeft veel menselijke handelingen niet nodig om te kunnen functioneren. De meeste select en selection statements zijn overbodig in code evenals veel parameters die excel macrorecorder uitspuugt zoals hier bij pastespecial.

een snelle opschoning levert dan iets op als dit.

Sheets("NIEUWE INVOER").Select
Range("B2", Range("B65000").End(xlUp)).Copy
Sheets("NIEUWE DATA").Range("F4").PasteSpecial Paste:=xlPasteValues

deze code doet precies hetzelfde

vooral als je veel handelingen gaat automatiseren is dit soort code een stuk leesbaarder als alle overbodige ballast eruit is.
vroeger gaf ik ook als argument "en een stuk sneller" maar met de huidige verwerkingskracht van pc's is dat meestal alleen nog van belang voor de puristen of perfectionisten. Zeker voor de meeste vba projectjes op forum.

Edit: en edmoor geeft hierboven weer een andere oplossing.
doel van mijn reactie is vooral inzicht in de werking van stukken code uit te leggen voor een beter begrip en toepassing later in andere situaties.

 

[edmoor]

Het nadeel ervan is dat je je huidige sheet verlaat, wat misschien niet de bedoeling is en ook weer de bijbehorende events triggert.

 

[JKlerks]

Edit: en edmoor geeft hierboven weer een andere oplossing.
doel van mijn reactie is vooral inzicht in de werking van stukken code uit te leggen voor een beter begrip en toepassing later in andere situaties.

Dank daarvoor in ieder geval. Ik probeer het inderdaad ook altijd op te schonen, maar dan vinden andere gebruikers het vaak lastig om te begrijpen wat er gebeurt en tevens leidde het wel eens tot foutmeldingen als ik wat teveel verwijder.