AW: Verstecken von Zeilen mit Bedingung
03.07.2008 11:21:28
Zeilen
Danke Daniel habs in der Zwischenzeit ueber VBA geloest :)
wens interessiert hier is der Code. (vergesst dabei die Datei die ich hochgeladen habe...) In spalte E steht der Customer Name. Ziel des Codes ists die rauszufiltern die doppelt sind und dann zu mergen. Und siehe da es klappt mit dem Code hier (ausgefuehrt wird das ganze ueber nen Button im Spreadsheet:
Sub Button1_Click()
'Trigger Variables
Dim buttonactivated As Boolean
'Temporary Storage Variables
Dim rowinteger As Integer
Dim rowbeforeinteger As Integer
Dim customer1string As String
Dim customer2string As String
Dim b As String
Dim j As Integer
'Important Constants
Const customercolumn = "E"
Const opportunitycolumn = "Y"
Const opportunityNACcolumn = "Z"
Const epsoldcolumn = "T"
Const emailsoldcolumn = "U"
Const websoldcolumn = "V"
Const nacsoldcolumn = "W"
'Unimportant Constants
Const usersoldcolumn = "I"
Const remarkscolumn = "X"
'Arrays and their Index Variables
Dim hidden As Integer
Dim HiddenRows() As Integer
Dim inserted As Integer
Dim InsertedRows() As Integer
Dim rightborder As Integer
b = ":"
If (buttonactivated = True) Then
'Shows all deals in full detail again
rightborder = UBound(HiddenRows, 1)
For j = 1 To rightborder
Rows(HiddenRows(j) & b & HiddenRows(j)).EntireRow.hidden = False
Next j
rightborder = UBound(InsertedRows, 1)
For j = 1 To rightborder
Rows(InsertedRows(j) & b & InsertedRows(j)).Delete
For i = j + 1 To rightborder
InsertedRows(i) = InsertedRows(i) - 1
Next
Next j
Rows((rowinteger - 10) & b & (rowinteger - 9)).EntireRow.hidden = False
Rows((rowinteger - 8) & b & (rowinteger - 7)).EntireRow.hidden = True
Range("AA" & (rowinteger - 9)).Value = "Details were unhided successfully. You are able to _
_
use the Switches now"
Range("AA" & (rowinteger - 7)).Select
Selection.Clear
buttonactivated = False
Else
'Summarizes all deals of the same customer.
customer2string = ""
hidden = 1
inserted = 1
For rowinteger = 2 To 1000
rowbeforeinteger = rowinteger - 1
If (Range(customercolumn & rowinteger).Value = Range(customercolumn & rowbeforeinteger). _
_
Value) Then
customer1string = Range(customercolumn & rowinteger).Value
If (customer1string customer2string) Then
customer2string = customer1string
Rows(rowbeforeinteger & b & rowbeforeinteger).EntireRow.Insert
ReDim Preserve InsertedRows(1 To inserted) As Integer
InsertedRows(inserted) = rowbeforeinteger
inserted = inserted + 1
'first fill of inserted summary row
Rows(rowinteger & b & rowinteger).EntireRow.Select
Selection.Copy
Rows(InsertedRows(inserted - 1) & b & InsertedRows(inserted - 1)).EntireRow. _
Select
ActiveSheet.Paste
Range(usersoldcolumn & InsertedRows(inserted - 1)).Value = "see details"
Range(remarkscolumn & InsertedRows(inserted - 1)).Value = "see details"
Range(opportunitycolumn & InsertedRows(inserted - 1)).Value = Range( _
opportunitycolumn & rowinteger).Value
Range(opportunityNACcolumn & InsertedRows(inserted - 1)).Value = Range( _
opportunityNACcolumn & rowinteger).Value
ElseIf (customer1string = customer2string) Then
'completion fills of inserted summary row
Range(opportunitycolumn & InsertedRows(inserted - 1)).Value = Range( _
opportunitycolumn & InsertedRows(inserted - 1)).Value + Range(opportunitycolumn & rowinteger). _
Value
Range(opportunityNACcolumn & InsertedRows(inserted - 1)).Value = Range( _
opportunityNACcolumn & InsertedRows(inserted - 1)).Value + Range(opportunityNACcolumn & _
rowinteger).Value
End If
Rows(rowinteger & b & rowinteger).EntireRow.hidden = True
ReDim Preserve HiddenRows(1 To hidden) As Integer
HiddenRows(hidden) = rowinteger
hidden = hidden + 1
ElseIf (Range(customercolumn & rowinteger).Value = "") Then
Exit For
End If
Next rowinteger
Range(opportunitycolumn & (rowinteger + 4), opportunityNACcolumn & (rowinteger + 4)).Cells. _
_
Select
Selection.Copy
Range(opportunitycolumn & (rowinteger + 6), opportunityNACcolumn & (rowinteger + 6)).Cells. _
_
Select
ActiveSheet.Paste
rightborder = UBound(HiddenRows, 1)
For j = 1 To rightborder
Range(opportunitycolumn & (rowinteger + 6)).Value = CLng(Range(opportunitycolumn & ( _
rowinteger + 6)).Value) - CLng(Range(opportunitycolumn & HiddenRows(j)).Value)
Range(opportunityNACcolumn & (rowinteger + 6)).Value = CLng(Range(opportunityNACcolumn & _
_
(rowinteger + 6)).Value) - CLng(Range(opportunityNACcolumn & HiddenRows(j)).Value)
Next j
Rows((rowinteger + 3) & b & (rowinteger + 4)).EntireRow.hidden = True
Rows((rowinteger + 5) & b & (rowinteger + 6)).EntireRow.hidden = False
Range("AA" & (rowinteger + 6)).Value = "Details were hided successfully. Do NOT use the _
Switches when data is hided! You have to unhide first."
Range("AA" & (rowinteger + 4)).Select
Selection.Clear
buttonactivated = True
End If
End Sub
Ganz am Ende steht nur noch der Codeteil fuers Veraendern der Summenberechnung das hat mit verstecken aber nur bedingt zu tun, da fuer die Summenberechnung in Excel automatisch leider versteckte Zeilen mitgenutzt werden (und ich bisher net wusste wie man das ausstellen kann, danke dafuer an Daniel das duerfte mit deiner Formel ja dann unnoetig geworden sein). Wer Fragen zu einem gleichen aehnlichen Problem hat dem kann ich denk ich mal jetzt helfen. Mail an gho-reg@web.de
Danke btw auch fuer den Tipp mit xls anstatt xlsx... Da haste voellig recht, auch wenn ich alle bemitleide die noch mit 2003 arbeiten muessen ;) 2007 is einfach stark, leider ziehts recht viel Systemressourcen.
Gruesse Gho.