Copy rows if cells in column "K" end with either "X" or "x".

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone,
The code that I put at your disposal copies all the lines including the cells in column "K" which are equal to "X" or "x".

Here is the current code :
VBA Code: 
Sub Copier_Oiseaux_Elevage()

Dim rLastRow As Long, i As Long, j As Long, m As Long
  Dim arr, brr
  Dim sourceSht As String, targetSht As String

  sourceSht = "Parents"
  targetSht = "Elevage"

Application.ScreenUpdating = False

  With Worksheets(sourceSht)
    rLastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
    arr = .Range("A2:k" & rLastRow)

    ReDim brr(1 To UBound(arr), 1 To 11)
    m = 0
    For i = 1 To UBound(arr)
       If arr(i, 11) = "X" Or arr(i, 11) = "x" Then
        m = m + 1
        For j = 1 To 11
            brr(m, j) = arr(i, j)
        Next
      End If
    Next
  End With

  With Worksheets(targetSht)
    .UsedRange.Offset(1, 0).Clear
    .Range("a2").Resize(UBound(brr), UBound(brr, 2)) = brr
  End With
Application.ScreenUpdating = True
End Sub

How can we modify it so that we can also copy the rows including the cells in column "K" which end in either "X" or "x".
Thank you for your contributions.

Here is a small part of my "Parents" sheet

Elevage.xlsm
ABCDEFGHIJK
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursCouleurElevage
2Ae27-054/2023 MAe27-010/2021 MMn96-034/2021 FLamback Léon0a 11m 3j5H502-07-20234TMâle
3Ae27-055/2023 FMn96-046/2019 MMn96-010/2021 FLamback Léon0a 10m 25j4B912-07-20234TFemellex
4Ae27-056/2023 FMn96-046/2019 MMn96-010/2021 FLamback Léon0a 10m 25j4B912-07-20234TFemelle
5Ae27-057/2023 FMn96-046/2019 MMn96-010/2021 FLamback Léon0a 10m 25j4B912-07-20234TFemelle
6Ae27-058/2023 FMn96-046/2019 MMn96-010/2021 FLamback Léon0a 10m 25j4B912-07-20234TFemelle
72024-059/2023 MMn96-046/2019 MMn96-010/2021 FLamback Léon0a 10m 25j5H912-07-20234TMâle
830247-060/2023 M3024-066/2020 M2207-032/2020 FLamback Léon1a 0m 13j5B3024-05-20234TMâleX
9Ae27-060/2023 FAe27-010/2021 MAe27-038/2022 FLamback Léon0a 10m 20j4B317-07-20234TFemelle
10Ae27-061/2023 FAe27-010/2021 MAe27-038/2022 FLamback Léon0a 10m 20j4B317-07-20234TFemelle
11Ae27-062/2023 FAe27-010/2021 MAe27-038/2022 FLamback Léon0a 10m 20j4B317-07-20234TFemelle
12Ae27-063/2023 FAe27-010/2021 MAe27-038/2022 FLamback Léon0a 10m 20j4B317-07-20234TFemelle X
13Ae27-064/2023 FAe27-010/2021 MAe27-038/2022 FLamback Léon0a 10m 20j4B317-07-20234TFemelle
14Ae27-065/2023 FAe27-021/2022 MAe27-020/2022 FLamback Léon0a 10m 20j4B817-07-20234TFemellex
15Ae27-066/2023 MAe27-018/2022 MMn96-045/2022 FLamback Léon0a 10m 23j5H1014-07-20234TMâle
16Ae27-067/2023 MAe27-018/2022 MMn96-045/2022 FLamback Léon0a 10m 23j5H1014-07-20234TMâle
17Ae27-068/2023 FAe27-018/2022 MMn96-045/2022 FLamback Léon0a 10m 23j4B1014-07-20234TFemelle
18Ae27-069/2023 MAe27-018/2022 MMn96-045/2022 FLamback Léon0a 10m 23j5H1014-07-20234TMâle X
19Ae27-070/2023 MAe27-022/2022 MAe27-031/2022 FLamback Léon0a 10m 20j5H617-07-20234TMâle
20Ae27-071/2023 FAe27-022/2022 MAe27-031/2022 FLamback Léon0a 10m 20j4B617-07-20234TFemelle
21Ae27-072/2023 MAe27-033/2022 MMn96-008/2022 FLamback Léon0a 10m 19j5H1118-07-20234TMâle
22Ae27-073/2023 MAe27-033/2022 MMn96-008/2022 FLamback Léon0a 10m 19j5H1118-07-20234TMâle
23Ae27-074/2023 FAe27-033/2022 MMn96-008/2022 FLamback Léon0a 10m 19j4B1118-07-20234TFemelle
24Ae27-001/2024 FAe27-010/2021 MAe27-017/2023 FLamback Léon0a 1m 13j5H1224-04-20244TFemelle Tte jauneElevé / Ae27-017/23 x
25Ae27-002/2024 MAe27-010/2021 MAe27-017/2023 FLamback Léon0a 1m 13j5H1224-04-20244TMâle Tt jauneElevé / Ae27-017/23
26Ae27-003/2024 FAe27-036/2023 MMn96-045/2022 FLamback Léon0a 1m 9j5H1328-04-20244TFemelle Tte jauneElevé / Ae27-017/23X
27Ae27-004/2024 FAe27-036/2023 MMn96-045/2022 FLamback Léon0a 1m 9j5H1328-04-20244TFemelle Tâche aile droiteElevé / Ae27-017/23
28Ae27-005/2024 MAe27-022/2023 MAe27-015/2023 FLamback Léon0a 1m 4j5H1101-05-20244TMâle Tte jauneElevé / Ae27-023/23 X
29Ae27-006/2024 MAe27-022/2023 MAe27-015/2023 FLamback Léon0a 1m 4j5H1101-05-20244TMâle Tâche derrière œil gaucheElevé / Ae27-023/23
30Ae27-007/2024 M5919-001/2023 MAe27-023/2023 FLamback Léon0a 1m 4j5H801-05-20244TMâle Tt jauneElevé / Ae27-023/23 X
31Ae27-008/2024 MAe27-033/2022 MAe27-009/2023 FLamback Léon0a 1m 4j5H401-05-20244TMâle Tâche au dosElevé / Ae27-023/23
32Ae27-009/2024 MAe27-033/2022 MAe27-009/2023 FLamback Léon0a 1m 4j5H401-05-20244TMâle Tâche au dosElevé / Mn96-045/22x
33Ae27-010/2024 MAe27-033/2022 MAe27-009/2023 FLamback Léon0a 1m 4j5H401-05-20244TMâle Tâche ronde à la têteElevé / Mn96-045/22
34Ae27-011/2024 MAe27-035/2022 MAe27-055/2023 FLamback Léon0a 1m 2j5H1603-05-20244TMâle
35Ae27-012/2024 MAe27-035/2022 MAe27-055/2023 FLamback Léon0a 1m 2j5H1603-05-20244TMâle
36Ae27-013/2024 MAe27-035/2022 MAe27-055/2023 FLamback Léon0a 1m 2j5H1603-05-20244TMâle
37Ae27-014/2024 MAe27-035/2022 MAe27-055/2023 FLamback Léon0a 1m 2j5H1603-05-20244TMâle
38Ae27-015/2024 MAe27-035/2022 MAe27-055/2023 FLamback Léon0a 1m 2j5H1603-05-20244TMâle
39Ae27-016/2024 MMn96-020/2023 MAe27-038/2022 FLamback Léon0a 0m 31j5H1006-05-20244TMâle
40Ae27-017/2024 MMn96-020/2023 MAe27-038/2022 FLamback Léon0a 0m 31j5H1006-05-20244TMâle
41Ae27-018/2024 MAe27-069/2023 MMn96-034/2021 FLamback Léon0a 0m 31j5H206-05-20244TMâle Elevé / Ae27-026/22
42Ae27-019/2024 MAe27-022/2022 MMn96-010/2021 FLamback Léon0a 0m 30j5H907-05-20244TMâle
43Ae27-020/2024 MAe27-022/2022 MMn96-010/2021 FLamback Léon0a 0m 30j5H907-05-20244TMâle
44Ae27-021/2024 MAe27-022/2022 MMn96-010/2021 FLamback Léon0a 0m 30j5H907-05-20244TMâle x
Parents


Unless I'm mistaken, here is the result to display in the "Elevage" sheet :

Elevage.xlsm
ABCDEFGHIJK
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursCouleurElevage
2Ae27-055/2023 FMn96-046/2019 MMn96-010/2021 FLamback Léon0a 10m 25j4B912-07-234TFemellex
330247-060/2023 M3024-066/2020 M2207-032/2020 FLamback Léon1a 0m 13j5B3024-05-234TMâleX
4Ae27-063/2023 FAe27-010/2021 MAe27-038/2022 FLamback Léon0a 10m 20j4B317-07-234TFemelle X
5Ae27-065/2023 FAe27-021/2022 MAe27-020/2022 FLamback Léon0a 10m 20j4B817-07-234TFemellex
6Ae27-069/2023 MAe27-018/2022 MMn96-045/2022 FLamback Léon0a 10m 23j5H1014-07-234TMâle X
7Ae27-001/2024 FAe27-010/2021 MAe27-017/2023 FLamback Léon0a 1m 13j5H1224-04-244TFemelle Tte jauneElevé / Ae27-017/23 x
8Ae27-003/2024 FAe27-036/2023 MMn96-045/2022 FLamback Léon0a 1m 9j5H13454104TFemelle Tte jauneElevé / Ae27-017/23X
9Ae27-005/2024 MAe27-022/2023 MAe27-015/2023 FLamback Léon0a 1m 4j5H11454134TMâle Tte jauneElevé / Ae27-023/23 X
10Ae27-007/2024 M5919-001/2023 MAe27-023/2023 FLamback Léon0a 1m 4j5H8454134TMâle Tt jauneElevé / Ae27-023/23 X
11Ae27-009/2024 MAe27-033/2022 MAe27-009/2023 FLamback Léon0a 1m 4j5H4454134TMâle Tâche au dosElevé / Mn96-045/22x
12Ae27-021/2024 MAe27-022/2022 MMn96-010/2021 FLamback Léon0a 0m 30j5H9454194TMâle x
Elevage
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Try modifying this line to make it:
VBA Code: 
If Right(arr(i, 11), 1) = "X" Or Right(arr(i, 11), 1) = "x" Then
 
Upvote 0
Solution
Hello rollis13,
Thanks for your feedback.
Adding the “Right” function works very well.
Thank you so much.
 
Upvote 0
Thanks for the positive feedback(y), glad having been of some help. By the way, you probably need to mark this thread as [Solved].
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
364
Cubist
Cubist
H
  • Solved
Capitalize the last character in column “K” conditionally.
Replies
3
Views
228
Fluff
Fluff
H
  • Solved
Delete the last character of cells in column "K" if it is equal to "X" or "x"
Replies
8
Views
149
Fuji
F
H
  • Solved
Retrieve the intersection of multiple cells with respect to columns and rows
2
Replies
10
Views
136
mumps
mumps
H
  • Solved
Search for topics based on their groups.
Replies
7
Views
400
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,259
Messages
6,177,480
Members
452,782
Latest member
ZCapitao

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top