Hello everyone,
My VBA skills do not allow me to solve my problem, hence my request to the experts among you.
The headers of my table are as indicated in the first line (see image). All the cells in this first line keep the same values except cell "E1" which changes according to the user's needs, this cell always contains a number which varies from 1 to 20 maximum.
To solve my problem, we will work mainly on column "A", let me explain.
The second cell (non-empty) of column "A" is cell "A10", for information, the line number of this 2nd cell is variable, hence the need to determine the line number and the value from its cell each time Macro is launched to place them in variables that we will use in the macro.
Next, we will determine the row of the 3rd (non-empty) cell of column "A", (in our case, it is row 24), the row number of this 3rd non-empty cell is variable, d 'where the need to determine its number each time the Macro is launched.
Next, we will determine the last cell of column "A", the number of the last line is variable, hence the need to determine the number of the last line each time the Macro is launched.
The goal of the code is to delete all lines (entire) that start at the line of the 3rd non-empty cell (In our case here, it is from line 24), and that end at the last line of the column "A", (in our present case, it is line no. 45), when the cells of column "A" are identical to the cells found in cells "D1", "E1","F1 " and "A6"
As my data is important and in order to allow the macro to be very fast, I would like to ask you to use tables with: For i = LBound(DelValues) To UBound(DelValues).
In summary, the cells to allow the tests to be carried out are colored yellow, the lines which must be deleted are colored green.
I remain at your disposal for further information.
Thank you for your contributions.
My VBA skills do not allow me to solve my problem, hence my request to the experts among you.
The headers of my table are as indicated in the first line (see image). All the cells in this first line keep the same values except cell "E1" which changes according to the user's needs, this cell always contains a number which varies from 1 to 20 maximum.
To solve my problem, we will work mainly on column "A", let me explain.
The second cell (non-empty) of column "A" is cell "A10", for information, the line number of this 2nd cell is variable, hence the need to determine the line number and the value from its cell each time Macro is launched to place them in variables that we will use in the macro.
Next, we will determine the row of the 3rd (non-empty) cell of column "A", (in our case, it is row 24), the row number of this 3rd non-empty cell is variable, d 'where the need to determine its number each time the Macro is launched.
Next, we will determine the last cell of column "A", the number of the last line is variable, hence the need to determine the number of the last line each time the Macro is launched.
The goal of the code is to delete all lines (entire) that start at the line of the 3rd non-empty cell (In our case here, it is from line 24), and that end at the last line of the column "A", (in our present case, it is line no. 45), when the cells of column "A" are identical to the cells found in cells "D1", "E1","F1 " and "A6"
As my data is important and in order to allow the macro to be very fast, I would like to ask you to use tables with: For i = LBound(DelValues) To UBound(DelValues).
In summary, the cells to allow the tests to be carried out are colored yellow, the lines which must be deleted are colored green.
I remain at your disposal for further information.
Thank you for your contributions.
| Classeur1.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Consanguinité | Nbre de générations | 20 | Max 20 Générations | ||||
| 2 | ||||||||
| 3 | 976-054/2017 M | |||||||
| 4 | 392-019/2018 M | |||||||
| 5 | 392-043/2017 F | |||||||
| 6 | 3024-066/2020 M | |||||||
| 7 | 2203-008/2015 M | |||||||
| 8 | 2207-067/2017 F | |||||||
| 9 | 2207-029-2014 F | |||||||
| 10 | 3024-060/2023 M | |||||||
| 11 | 2207-079/2015 M | |||||||
| 12 | 2207-089/2018 M | |||||||
| 13 | 2207-021/2017 F | |||||||
| 14 | 2207-032/2020 F | |||||||
| 15 | 2207-058/2017 M | |||||||
| 16 | 2207-006/2019 F | |||||||
| 17 | 2207-029/2014 F | |||||||
| 18 | ||||||||
| 19 | ||||||||
| 20 | ||||||||
| 21 | ||||||||
| 22 | ||||||||
| 23 | ||||||||
| 24 | Max 20 Générations | 3 | ||||||
| 25 | 3024-060/2023 M | 2 | ||||||
| 26 | Max 20 Générations | 2 | ||||||
| 27 | 3024-060/2023 M | 1 | ||||||
| 28 | 392-019/2018 M | 1 | ||||||
| 29 | 392-043/2017 F | 1 | ||||||
| 30 | Nbre de générations | 1 | ||||||
| 31 | 2203-008/2015 M | 1 | ||||||
| 32 | 2207-067/2017 F | 1 | ||||||
| 33 | 3024-060/2023 M | 1 | ||||||
| 34 | Nbre de générations | 1 | ||||||
| 35 | 2207-089/2018 M | 1 | ||||||
| 36 | 3024-060/2023 M | 1 | ||||||
| 37 | Nbre de générations | 1 | ||||||
| 38 | 2207-058/2017 M | 1 | ||||||
| 39 | 20 | 1 | ||||||
| 40 | Nbre de générations | 1 | ||||||
| 41 | 20 | 1 | ||||||
| 42 | Max 20 Générations | 1 | ||||||
| 43 | 20 | 1 | ||||||
| 44 | Nbre de générations | 1 | ||||||
| 45 | Max 20 Générations | 1 | ||||||
Pedigree | ||||||||
