Mit VBA auf anderes Tabellenblatt verweisen

cedrico · 11. November 2019 um 21:35

Guten Tag

Ich habe ein einfaches Makro, welches leere Zeilen eines gewissen Ranges ausblendet.

Nun möchte ich aber, dass mit dem gleichen Makro (oder auch mit einem Neuen auf dem 1. Tabellenblatt) ebenfalls leere Zeilen im Bereich F9 bis F65 des 2. Tabellenblattes („Print2“) ausgeblendet werden. Kann mir da jemand helfen?

Mein VBA-Code:
Private Sub CommandButton1_Click()

Dim objCell As Range

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

For Each objCell In Range(„A17:A162“)
objCell.EntireRow.Hidden = objCell.Value = „“
Next objCell

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

Thomas_Ramel · 11. November 2019 um 21:36

Grüezi cedrico

Ich habe ein einfaches Makro, welches leere Zeilen eines
gewissen Ranges ausblendet.

Nun möchte ich aber, dass mit dem gleichen Makro (oder auch
mit einem Neuen auf dem 1. Tabellenblatt) ebenfalls leere
Zeilen im Bereich F9 bis F65 des 2. Tabellenblattes („Print2“)
ausgeblendet werden. Kann mir da jemand helfen?

Ich habe den Code mal etwas eingekürzt und deinen Wunsch integriert - den Namen der Tabellenblätter musst Du ev. noch anpassen:

Private Sub CommandButton1\_Click()
 With Application
 .ScreenUpdating = False
 .Calculation = xlCalculationManual
 .EnableEvents = False

 Worksheets("Tabelle1").Range("A17:A162").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
 Worksheets("Print2").Range("F9:F65").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

 .ScreenUpdating = True
 .Calculation = xlCalculationAutomatic
 .EnableEvents = True
 End With
End Sub

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 11. November 2019 um 21:36

Grüezi Cedrico, Thomas,

jaaa, ich lass ErrorHandler usw. hier oft auch weg
Aber Specialcells sind da irgendwie sehr speziell wenn sie nix finden.
Und, es gibt noch eine Winzigkeit, die normal nicht auftaucht, aber auftauchen könnte mit anderem Code der was macht.

@Cedrico, schreib das bitte so:

CountIf entspricht exakt Zählenwenn, schau da in der Hilfe nach der Syntax, die gibst du da ein in Vba-Schreibweise.

Nicht

Worksheets("Tabelle1").Range("A17:A162").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
Worksheets("Print2").Range("F9:F65").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

Sondern so

Worksheets("Tabelle1").Rows("17:162").Hidden=False
if application.countif(...) \>0 then
 worksheets("Tabelle1").Range("A17:A162").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
end if

Worksheets("Print2").Rows("9:65").Hidden=False
if application.countif(...) \>0 then
 Worksheets("Print2").Range("F9:F65").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
end if

Gruß
Reinhard

Thomas_Ramel · 11. November 2019 um 21:36

Grüezi Reinhard

jaaa, ich lass ErrorHandler usw. hier oft auch weg

Aber Specialcells sind da irgendwie sehr speziell wenn sie
nix finden.

Wo Du recht hast, hast Du recht…

Aaaber…

CountIf entspricht exakt Zählenwenn, schau da in der Hilfe
nach der Syntax, die gibst du da ein in Vba-Schreibweise.

Sondern so

Worksheets(„Tabelle1“).Rows(„17:162“).Hidden=False

if application.countif(…) >0 then

worksheets(„Tabelle1“).Range(„A17:A162“).SpecialCells(xlCellTyp
eBlanks).EntireRow.Hidden = True

end if

Worksheets(„Print2“).Rows(„9:65“).Hidden=False

if application.countif(…) >0 then

Worksheets(„Print2“).Range(„F9:F65“).SpecialCells(xlCellTypeBla
nks).EntireRow.Hidden = True

end if

ZÄHLENWENN() und leere Zellen sind nicht gerade gut freund, daher würe ich das so schreiben:

Private Sub CommandButton1\_Click()
 With Application
 .ScreenUpdating = False
 .Calculation = xlCalculationManual
 .EnableEvents = False

 With Worksheets("Tabelle1").Range("A17:A162")
 .Cells.EntireRow.Hidden = False
 If Application.WorksheetFunction.CountBlank(.Cells) \> 0 Then
 .SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
 End If
 End With

 With Worksheets("Print2").Range("F9:F65")
 .Cells.EntireRow.Hidden = False
 If Application.WorksheetFunction.CountBlank(.Cells) \> 0 Then
 .SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
 End If
 End With

 .ScreenUpdating = True
 .Calculation = xlCalculationAutomatic
 .EnableEvents = True
 End With
End Sub

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

cedrico · 11. November 2019 um 21:36

Erstmal vielen Dank für Eure schnellen Antworten!

Beim Code von Thomas Ramel kommt bei mir immer der Laufzeitfehler 1004 („keine Zellen gefunden“).

Und beim Vorschlag von Reinhard der Laufzeitfehler 9 („Index ausserhalb des gültigen Bereichs“), vermutlich da ich die Syntax beim Countif falsch eingegeben habe. (Application.CountIf(F9:F65;"")).

Noch anzumerken ist, dass die betroffenen Zellen nicht wirklich leer sind, sondern die Formel in der Zelle einfach keine Daten wiedergibt.

Es sollte dabei nur geprüft werden, ob die Zellen A17-A162 auf dem 1. Tabellenblatt („Print“), sowie F9-F65 auf Print2 leer ("") sind. Falls dies zutrifft, sollte die ganze Zeile ausgeblendet werden.

Gruss
cedrico

Thomas_Ramel · 11. November 2019 um 21:36

Grüezi cedrico

Beim Code von Thomas Ramel kommt bei mir immer der
Laufzeitfehler 1004 („keine Zellen gefunden“).

Welchen meiner Codes hast Du denn geprüft?
Der im letzten Beitrag hat extra eine Fehlerprüfung drin, die solches verhindert.

Noch anzumerken ist, dass die betroffenen Zellen nicht
wirklich leer sind, sondern die Formel in der Zelle einfach
keine Daten wiedergibt.

Na, du bist mir ja ein Spassvogel!!

Und warum schreibst du das nicht gleich zu Beginn?

Damit fällt die SpecialCells()-Methode dann flach und wir beginnen nochmals - als ob wir nichts anderes zu tun hätten…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 11. November 2019 um 21:36

Hallo Cedrico,

Beim Code von Thomas Ramel kommt bei mir immer der
Laufzeitfehler 1004 („keine Zellen gefunden“).

Und beim Vorschlag von Reinhard der Laufzeitfehler 9 („Index
ausserhalb des gültigen Bereichs“), vermutlich da ich die
Syntax beim Countif falsch eingegeben habe.
(Application.CountIf(F9:F65;"")).

das find ich gut daß auch beim Code vom Thomas Fehler auftreten *lach*, kriegen wir schon hin.
Ja, du hast da einen Syntaxfehler bei meinem Vorschalg.
Application.CountIf(F9:F65;"")
geht nicht.
Vba-Schreibweise sieht so aus:
IF Application.CountIf(Range(„F9:F65“), „“) > 0 Then

aber, Countif/Zählenwenn, da hat Thomas Recht, funtioniert nicht zuverlässig bei Leerzellen, also folge dem was Thomas dir sagt.

Noch anzumerken ist, dass die betroffenen Zellen nicht
wirklich leer sind, sondern die Formel in der Zelle einfach
keine Daten wiedergibt.

Nicht unwichtig zu wissen.

Gruß
Reinhard

cedrico · 11. November 2019 um 21:36

@ Thomas Ramel und Reinhard: Tut mir leid, ich habe gedacht, das sei aus meinem alten Code ersichtlich. Deswegen habe ich es nicht zusätzlich erwähnt. Sorry!

@ Reinhard: Habe nun die VBA-Schreibweise angewandt, kommt nun auch der gleiche Laufzeitfehler wie bei Thomas Ramel

Thomas_Ramel · 11. November 2019 um 21:36

Grüezi cedrico

@ Thomas Ramel und Reinhard: Tut mir leid, ich habe gedacht,
das sei aus meinem alten Code ersichtlich. Deswegen habe ich
es nicht zusätzlich erwähnt. Sorry!

Wie, denkst Du, kann eine Zelle leer sein, wenn da eine Formel drin steht?

…und mit deinem Code kann eben beides der Fall sein, daher bin ich nach deinen Worten gegangen, die stets von ‚leeren Zellen‘ handelten.

…und Fragen magst Du auch nicht beantworten?
Welchen meiner Codes hast Du denn nun getestet?

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 11. November 2019 um 21:37

@ Thomas Ramel und Reinhard: Tut mir leid, ich habe gedacht,
das sei aus meinem alten Code ersichtlich. Deswegen habe ich
es nicht zusätzlich erwähnt. Sorry!

Hallo Cedrico,

probiere es mal so:

Option Explicit

Private Sub CommandButton1\_Click()
Dim Zelle As Range
Call Schnell(False)
For Each Zelle In Worksheets("Tabelle1").Range("A17:A162").Cells
 Zelle.EntireRow.Hidden = IIf(Zelle.Value = "", True, False)
Next Zelle
For Each Zelle In Worksheets("Tabelle2").Range("F9:F65").Cells
 Zelle.EntireRow.Hidden = IIf(Zelle.Value = "", True, False)
Next Zelle
Call Schnell(True)
End Sub

Sub Schnell(AUSEIN As Boolean)
With Application
 .ScreenUpdating = AUSEIN
 .Calculation = IIf(AUSEIN, xlCalculationAutomatic, xlCalculationManual)
 .EnableEvents = AUSEIN
End With
End Sub

Gruß
Reinhard

cedrico · 11. November 2019 um 21:37

Guten Morgen Reinhard

Vielen Dank für deine Hilfe. Dein Code funktioniert perfekt.

Tut mir leid für die Unannehmlichkeiten wegen meinen zuerst ungenauen Angaben. Herzlichen Dank nochmals!

Grüsse und einen schönen Tag

Cedrico

P.S. @ Thomas Ramel: Ich wusste nicht, dass sich mein Code auf beide Problemstellungen beziehen kann. Danke für die Belehrung!

Ich habe beide deiner Codes ausprobiert, kam bei beiden der selbe Laufzeitfehler. Trotzdem vielen Dank für deine Hilfe und nochmals sorry für die Unannehmlichkeiten.

Thomas_Ramel · 11. November 2019 um 21:38

Grüezi cedrico

P.S. @ Thomas Ramel::
Ich habe beide deiner Codes ausprobiert, kam bei beiden der
selbe Laufzeitfehler.

Ja, weil eben die Zellen nicht leer sind, sondern nur so aussehen…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -