De laatste cel opnieuw instellen in Excel

[Maurice40]

Ik loop tegen een probleem aan met het zoeken naar de eerstevolgende lege cel in in een kolom met gegevens.
Ik gebruik de code:

Cells(Rows.Count, 1).End(xlUp).Cells(2, 1).Select

Als cel A20 de laatste cel met inhoud is, dan wordt niet A21 geselecteerd maar A25.

Dit blijkt een bekend probleem te zijn in excel, echter alle mogelijke oplosingen die ik op het internet kan vinden die werken niet op mijn excel-bestand.

De volgende mogelijke oplossingen heb ik geprobeerd:
1 In direct venster:
ActiveSheet.UsedRange + enter
als ik het voorbeeld uit http://www.accountingweb.com/article/resetting-last-cell-excel-worksheet/221618 uitvoer, werkt het wel. In mijn excel-bestand werkt het niet.
2 de Sub DeleteUnused() uit http://www.contextures.com/xlfaqApp.html#Unused
geeft een foutmelding
3 de invoegtoepassing Excess Format Cleaner 1.1
geeft een foutmelding

Ik heb een voorbeeld excelbestand toegevoegd Bekijk bijlage lege cellen verwijderen.xlsm
Als je op de knop verwerken klikt, dan zullen gegevens in het blad data gekopieerd worden.

Wie vind er een oplossing voor mij?

 

[pixcel]

Je probleem ontstaat doordat je vanaf rij 8 gegevens hebt staan, in de vorm van formules. Je ziet ze niet maar voor excel zijn ze er wel. Dit "vooruit werken" is sinds excel 2007 niet meer nodig door gebruik te maken van tabellen (Invoegen > tabel). Als je rijen toevoegt kopieert excel de formules voor je in de nieuwe rij.

 

[HSV]

Of:

Sub verwerken()
Application.ScreenUpdating = False
With Sheets("verwerken")
 .Range(.Range("E1", .Range("E1", .Cells(1, Columns.Count).End(xlToLeft))), .Columns(5).SpecialCells(-4123, 1)).Copy
   Sheets("data").Range("A1").PasteSpecial xlValues
 End With
 Application.CutCopyMode = False
End Sub

 

[Maurice40]

bedankt voor je reactie.
De oplossing die je aandraagt is nieuw voor mij. Ik ken die mogelijkheid niet. Zou je kunnen voordoen hoe dat in mijn excel-bestandje dan in zijn werk gaat?

 

[Maurice40]

Hallo harry,
hoe wordt de formule als je de bewerking laat plaatsvinden op het blad "data"?

 

[Maurice40]

Harry, ik begrijp nu wat je gemaakt hebt. Echter, als je in "data" naar laatste gebruikte cel gaat, wordt cel A11 geselecteerd en niet A7.

 

[HSV]

Dag Maurice,

Code blijft hetzelfde.

Wat @Frans bedoeld is menu Invoegen → Tabel.
Telkens als je een rij toevoegd aan de tabel worden de formules automatisch toegevoegd.

 

[Maurice40]

oke, ik moet handmatig een regel toevoegen.
Het bestandje dat ik heb bijgevoegd is een versimpelde versie van het origineel.
De bedoeling van het originele excel-bestand is het volgende:
Het excelbestand haalt bepaalde gegevens uit een pdf file met woningadressen. Die gegevens worden vervolgens in het data-blad geachiveerd. De hoeveelheid gegevens die uit een pdf file gehaald worden, is afhankelijk van hoeveel woningadressen er op een pdf-file vermeld staan en verschilt dus per keer.

In feite copieer ik alle gegevens van een pdf-file en plak die in een kolom in mijn excelblad"verwerken". Hier worden alle relevante gegevens uit de pdf-file verzameld. Dan activeer ik de macro "verwerken" en die zorgt ervoor dat al die relevante gegevens in het tabblad "data" op de eerstvolgede lege regel geplakt worden.

Volgens mij werkt de optie met invoegen tabel dan niet.

 

[Maurice40]

iemand nog een andere suggestie hoe de laatste cel in het blad data te resetten?

 

[sylvester-ponte]

in jouw bestandje werkt Harry's macro prima
als je een bestandje hebt waar het niet werkt moet je het even posten
en laten zien hoe het fout gaat

 

[pixcel]

Volgens mij werkt de optie met invoegen tabel dan niet.

Laat eens zien wat je al geprobeerd hebt en wat er dan niet lukt.

 

[Maurice40]

ik denk dat ik jullie op een verkeerd spoor gezet heb met het bestandje wat ik heb toegevoegd. Ik heb dat bestandje gebouwd om het probleem waar ik tegenaan loop zichtbaar te maken. Het excel bestandje opzich heeft totaal geen functie.

Als ik het originele bestand toevoeg dan wordt het waarschijnlijk wel duidelijk, maar ik kan het niet toevoegen, omdat het te groot is. Het is gezipt 264 kb...

 

[pixcel]

We vragen niet om het originele bestand maar om een nieuw voorbeeld om ons weer op het juiste spoor te krijgen

 

[Maurice40]

ik heb originele excel-file verkleind. Nu past het wel. Ik heb er ook 2 pdf-files bijgevoegd.
Uit deze pdf-files moet ik gegevens in excel verwerken. Ik krijg op een dag heel veel van deze pdf-files binnen, die ik moet verwerken. Vandaar dat ik dit probeer te automatiseren.

De werkwijze:
Open een pdf file->alles selecteren-> kopieren-> plakken in cel A1 van het blad "Glas bewerken".
(er worden nu een aantal adressen zichtbaar. Deze adressen moeten nu automatisch in het blad "Glas data" gekopieerd worden.)
Start Marco "gegevens_kopieren_naar_data_glas"->sluit deelformulier.
(De adressen staan nu in het blad "Glas data")

Open nu het tweede pdf file en volg dezelfde procedure als hierboven.

Je ziet nu in het blad "Glas data" 49 lege rijen tussen de adressen uit het eerste pdf-file en het tweede pdf-file. Hoe kan ik dit voorkomen?

PS.: Ook is het zo dat het aantal adressen in een pdf-file per pdf-file verschilt. Vandaar dat ik "vooruit werk" zoals pixcel dit in een vorige reactie noemt.

 

[Maurice40]

nu met goede excel file

 

[Maurice40]

oplossing:
Sub gegevens_kopieren_naar_data_glas()
'
' gegevens_kopieren_naar_data_glas Macro
'

'
Dim c As Range, arr
With Sheets("Glas bewerken")
Set c = .Columns("H:H").Find("", lookat:=xlWhole, LookIn:=xlValues)
If Not c.Address = "$H$2" Then Set c = c.Offset(-1)
Debug.Print c.Address
arr = .Range(c, .Range("AA2")).Value
.Columns("A:A").ClearContents
With .Range("A1")
.Value = "Open PDF van de aanvraag (niet PDF verkort ! ), selecteer alles (Ctrl A), daarna copieren (Ctrl C) en in deze cel plakken."
.Font.Name = "Comic Sans MS"
.Characters(Start:=27, Length:=4).Font.Underline = xlUnderlineStyleSingle
End With
End With
With Sheets("Glas data")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(arr), UBound(arr, 2)).Value = arr
End With


End Sub

Bedankt voor het meedenken