Προς το περιεχόμενο

Pos mporo na kano me vba afto sto excel?

oldnew

Από τον oldnew
στο Software

Προτεινόμενες αναρτήσεις

oldnew Grand Master

oldnew

Δημοσ.
Δημοσ.

Gia. Thelo na kano mia makroentolh poy na kanei to exsis:

Na exo kanei apokropsh sth gramh 3, kai otan patao sto kelh (ths prohgoymenhs gramhs ) A2, na emfanizetai h gramh 3. An sviso oti exo grapsei sto keli A2, na ksanaginetai apokripsh sth gramh 3. Me koymia vba, ksero na to kano, pos mporo na to elenkso afto ,oxi me koympi ,ala apla patontas ena sinkekrimeno keli ,gia thn akrivia se ena apo 10 kelia , as poume otan grafo kati se ena keli apo to A2 eos I2, na emfanizetai h gramh 3...

An kserei kapoios as moy apantisei.

Eyxaristo prokatavolika.

kounoupi Proficient

kounoupi

Δημοσ.
Δημοσ.

Πρέπει να είναι στο sheet που θες να παρακολουθήσεις

 

>Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Not Intersect(Target, Range("[color=Red]A2:I2[/color]")) Is Nothing Then
   If WorksheetFunction.CountA(Worksheets("[color=Red]Sheet1[/color]").Range("[color=Red]A2:I2[/color]")) = 0 Then
       Rows("3:3").Select
       Selection.EntireRow.Hidden = True
       Range("A1").Select
   Else
       Rows("3:3").Select
       Selection.EntireRow.Hidden = False
       Range("A1").Select
   End If
End If

End Sub

Πες μου αν έπεσα διάνα :-D

oldnew Grand Master

oldnew

Δημοσ.
  • Μέλος
Δημοσ.

File moy xilia eyxaristo. Afto hthela, den iksera pos ginetai.

Den soy eixa apanthsei toso kairo epeidi eixa ena provlima me thn adsl kai den mporoysa na apanthso (diavaza ,ala den mporoysa na grapso.. apisteyto e?)

To tropopoihsa "elafros" to scriptaki sou, h vasikh idea einai h idia omos.

Den to exo teleiosei akoma, ala kai etsi opoios to thelei mporei na to dokimasei na dei ti kanei. Einai xrhsimo gia opoion ftiaxnei sto excel, p.x listes gia eisagogh timologion, klp, oste na mhn katalamvanei poli xoro, to shmeio poy kanoume eisagogh. Telos panton gia opoion endiaferetai as to dokimasei na dei ti kanei kai an mporei na tou xrhsimeysei.

 

 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Not Intersect(Target, Range("A2:I20")) Is Nothing Then

'metablhtes statheres pantoy

sthlharxh = 1

sthlhtelos = 10

theshkersorax = 1

 

'metablhtes poy alazoyn kata tmhma

gramia = 2

thesh = 0

theshkersorathesh = 0

kersoras = 2

'2

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia, sthlharxh), Cells(gramia, sthlhtelos))) = 0 Then

'apokripsh gramhs 3

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 1, sthlharxh), Cells(gramia + 1, sthlhtelos))) = 0 Then

Cells(3 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(2 + theshkersorathesh, theshkersorax).Select

 

End If

 

End If

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia, sthlharxh), Cells(gramia, sthlhtelos))) = 1 Then

'emfanish gramhs 3

Cells(3 + thesh, 1).Select

Selection.EntireRow.Hidden = False

 

Cells(3 + theshkersorathesh, theshkersorax).Select

kersoras = 3 + thesh

End If

 

 

 

 

'3

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 1, sthlharxh), Cells(gramia + 1, sthlhtelos))) = 0 Then

'apokripsh gramhs 4

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 2, sthlharxh), Cells(gramia + 2, sthlhtelos))) = 0 Then

Cells(4 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(3 + theshkersorathesh, theshkersorax).Select

End If

 

End If

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 1, sthlharxh), Cells(gramia + 1, sthlhtelos))) = 1 Then

'emfanish gramhs 3

Cells(4 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(4 + theshkersorathesh, theshkersorax).Select

kersoras = 4 + thesh

End If

 

 

 

'***** apo edo kai pera idios kodikas

 

'4 start

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 2, sthlharxh), Cells(gramia + 2, sthlhtelos))) = 0 Then

'apokripsh gramhs 5

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 3, sthlharxh), Cells(gramia + 3, sthlhtelos))) = 0 Then

Cells(5 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(4 + theshkersorathesh, theshkersorax).Select

End If

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 1, sthlharxh), Cells(gramia + 1, sthlhtelos))) = 0 Then

Cells(4 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(5 + theshkersorathesh, theshkersorax).Select

End If

 

End If

'end

 

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 2, sthlharxh), Cells(gramia + 2, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(5 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(5 + theshkersorathesh, theshkersorax).Select

kersoras = 5 + thesh

End If

 

 

 

'5

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 3, sthlharxh), Cells(gramia + 3, sthlhtelos))) = 0 Then

'apokripsh gramhs 6

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 4, sthlharxh), Cells(gramia + 4, sthlhtelos))) = 0 Then

Cells(6 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(5 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 2, sthlharxh), Cells(gramia + 2, sthlhtelos))) = 0 Then

Cells(5 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(6 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 3, sthlharxh), Cells(gramia + 3, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(6 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(6 + theshkersorathesh, theshkersorax).Select

kersoras = 6 + thesh

End If

 

 

 

 

'6

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 4, sthlharxh), Cells(gramia + 4, sthlhtelos))) = 0 Then

'apokripsh torinhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 5, sthlharxh), Cells(gramia + 5, sthlhtelos))) = 0 Then

Cells(7 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(6 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 3, sthlharxh), Cells(gramia + 3, sthlhtelos))) = 0 Then

Cells(6 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(7 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 4, sthlharxh), Cells(gramia + 4, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(7 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(7 + theshkersorathesh, theshkersorax).Select

kersoras = 7 + thesh

End If

 

 

 

'7

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 5, sthlharxh), Cells(gramia + 5, sthlhtelos))) = 0 Then

'apokripsh torinhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 6, sthlharxh), Cells(gramia + 6, sthlhtelos))) = 0 Then

Cells(8 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(7 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 4, sthlharxh), Cells(gramia + 4, sthlhtelos))) = 0 Then

Cells(7 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(8 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 5, sthlharxh), Cells(gramia + 5, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(8 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(8 + theshkersorathesh, theshkersorax).Select

kersoras = 8 + thesh

End If

 

 

 

 

 

 

 

'8

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 6, sthlharxh), Cells(gramia + 6, sthlhtelos))) = 0 Then

'apokripsh torinhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 7, sthlharxh), Cells(gramia + 7, sthlhtelos))) = 0 Then

Cells(9 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(8 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 5, sthlharxh), Cells(gramia + 5, sthlhtelos))) = 0 Then

Cells(8 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(9 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 6, sthlharxh), Cells(gramia + 6, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(9 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(9 + theshkersorathesh, theshkersorax).Select

kersoras = 9 + thesh

End If

 

 

 

 

 

'9

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 7, sthlharxh), Cells(gramia + 7, sthlhtelos))) = 0 Then

'apokripsh torinhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 8, sthlharxh), Cells(gramia + 8, sthlhtelos))) = 0 Then

Cells(10 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(9 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 6, sthlharxh), Cells(gramia + 6, sthlhtelos))) = 0 Then

Cells(9 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(10 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 7, sthlharxh), Cells(gramia + 7, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(10 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(10 + theshkersorathesh, theshkersorax).Select

kersoras = 10 + thesh

End If

 

 

'10

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 8, sthlharxh), Cells(gramia + 8, sthlhtelos))) = 0 Then

'apokripsh torinhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 9, sthlharxh), Cells(gramia + 9, sthlhtelos))) = 0 Then

Cells(11 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(10 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 7, sthlharxh), Cells(gramia + 7, sthlhtelos))) = 0 Then

Cells(10 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(11 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 8, sthlharxh), Cells(gramia + 8, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(11 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(11 + theshkersorathesh, theshkersorax).Select

kersoras = 11 + thesh

End If

 

 

'11

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 9, sthlharxh), Cells(gramia + 9, sthlhtelos))) = 0 Then

'apokripsh torinhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 10, sthlharxh), Cells(gramia + 10, sthlhtelos))) = 0 Then

Cells(12 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(11 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 8, sthlharxh), Cells(gramia + 8, sthlhtelos))) = 0 Then

Cells(11 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(12 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 9, sthlharxh), Cells(gramia + 9, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(12 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(12 + theshkersorathesh, theshkersorax).Select

kersoras = 12 + thesh

End If

 

 

 

'12

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 10, sthlharxh), Cells(gramia + 10, sthlhtelos))) = 0 Then

'apokripsh torinhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 11, sthlharxh), Cells(gramia + 11, sthlhtelos))) = 0 Then

Cells(13 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(12 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 9, sthlharxh), Cells(gramia + 9, sthlhtelos))) = 0 Then

Cells(12 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(13 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 10, sthlharxh), Cells(gramia + 10, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(13 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(13 + theshkersorathesh, theshkersorax).Select

kersoras = 13 + thesh

End If

 

 

 

 

 

 

'13

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 11, sthlharxh), Cells(gramia + 11, sthlhtelos))) = 0 Then

'apokripsh torinhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 12, sthlharxh), Cells(gramia + 12, sthlhtelos))) = 0 Then

Cells(14 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(13 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 10, sthlharxh), Cells(gramia + 10, sthlhtelos))) = 0 Then

Cells(13 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(14 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 11, sthlharxh), Cells(gramia + 11, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(14 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(14 + theshkersorathesh, theshkersorax).Select

kersoras = 14 + thesh

End If

 

 

'14

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 12, sthlharxh), Cells(gramia + 12, sthlhtelos))) = 0 Then

'apokripsh torinhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 13, sthlharxh), Cells(gramia + 13, sthlhtelos))) = 0 Then

Cells(15 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(14 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 11, sthlharxh), Cells(gramia + 11, sthlhtelos))) = 0 Then

Cells(14 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(15 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 12, sthlharxh), Cells(gramia + 12, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(15 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(15 + theshkersorathesh, theshkersorax).Select

kersoras = 15 + thesh

End If

 

 

 

'15

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 13, sthlharxh), Cells(gramia + 13, sthlhtelos))) = 0 Then

'apokripsh torinhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 14, sthlharxh), Cells(gramia + 14, sthlhtelos))) = 0 Then

Cells(16 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(15 + theshkersorathesh, theshkersorax).Select

End If

' elenxos prohg gramhs kai an einai kenh diagrafh ths torinhs eksetazomenhs gramhs

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 12, sthlharxh), Cells(gramia + 12, sthlhtelos))) = 0 Then

Cells(15 + thesh, 1).Select

Selection.EntireRow.Hidden = True

Cells(16 + theshkersorathesh, theshkersorax).Select

End If

End If

'end

If WorksheetFunction.CountA(Worksheets("Sheet3").Range(Cells(gramia + 13, sthlharxh), Cells(gramia + 13, sthlhtelos))) = 1 Then

'emfanish gramhs

Cells(16 + thesh, 1).Select

Selection.EntireRow.Hidden = False

Cells(16 + theshkersorathesh, theshkersorax).Select

kersoras = 15 + thesh

End If

 

 

 

Cells(kersoras, 1).Select

End If

End Sub

Αρχειοθετημένο

Αυτό το θέμα έχει αρχειοθετηθεί και είναι κλειστό για περαιτέρω απαντήσεις.

Προς την λίστα θεμάτων
  • Δημιουργία νέου...