Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Select choosen rows

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Select choosen rows

    Hello MrExcel,
    I have data in Range("A1:C100000"). In column A I have number accounts and I would like to have macro which select all (not filter) this rows which fullfilled conditions.
    Thanks a lot for your help.


    Best regards
    Robert

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    10,296
    Post Thanks / Like
    Mentioned
    195 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Select choosen rows

    Hi & welcome to the board.
    A few questions
    1) What are the conditions?
    2) What do you want to do with the selected data?
    3) What does you data look like?
    There are some tools available here to enable you to post data to the site & a test board here where you can test them.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Jul 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select choosen rows

    Hi,
    thx a lot for your fast response
    In column A I've a number accounts, for example:
    a101
    a102
    a103
    a104
    a105
    .
    .
    .
    a1011

    and I would like to select all this rows which have in column A for example: a115:a118
    when I select all this rows I want to add new row.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    10,296
    Post Thanks / Like
    Mentioned
    195 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Select choosen rows

    How will the macro know which accounts you want to select & where do you want to add the new row?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  5. #5
    New Member
    Join Date
    Jul 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select choosen rows

    I've input box in macro, where I enter account number. When I enter account number macro should select all rows with this account number and add rows above this rows.
    Honestly I have problem how to write code line with select all rows at once

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    10,296
    Post Thanks / Like
    Mentioned
    195 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Select choosen rows

    What code do you currently have?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  7. #7
    New Member
    Join Date
    Jul 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select choosen rows

    My code below:
    Sub add_rows()
    Dim zakres As String
    accnum = InputBox("Enter account number")
    For i = 30763 To 100000


    If Cells(i, 3) = accnum Then
    arr1 = i & ":" & i + 7
    arr = arr & "," & arr1
    End If


    Next


    arr = Right(arr, Len(arr) - 1)


    Range(arr).Select
    End Sub

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    10,296
    Post Thanks / Like
    Mentioned
    195 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Select choosen rows

    I'm afraid I still don't understand what you are trying to do.
    In your op you said
    In column A I have number accounts
    yet the code you've supplied is looking in Col C not A.
    You seem to be adding 7 rows at a time, which you have made no mention of.
    You're starting at row 30763. Is that fixed or will it change?
    How many rows do you need to add? Is just 1 row above each instance of the account number?
    Please try to explain exactly what you are trying to do. Bear in mind I cannot see your data & have no idea what you want to do.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,252
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Select choosen rows

    Assuming you want to search all rows for the value you enter into the Input Box
    And assuming we will be searching column A for the account number
    And assuming you want one row inserted every time the value is found

    Try this:
    Code:
    Sub add_rows()
    'Modified 6/11/18 10:15 AM EDT
    Application.ScreenUpdating = False
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Dim accnum As String
    accnum = InputBox("Enter account number")
    For i = Lastrow To 2 Step -1
        If Cells(i, 1).Value = accnum Then Rows(i).Insert
    Next
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    New Member
    Join Date
    Jul 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select choosen rows

    this line:
    Rows(i).Insert

    insert only one line of row, what if I want insert a few line of rows?

    is it possible to select all i-rows and then insert row/-s?

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com