Select choosen rows

[gosuansa]

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

 

[Fluff]

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.

 

[gosuansa]

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.

 

[Fluff]

How will the macro know which accounts you want to select & where do you want to add the new row?

 

[gosuansa]

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

 

[Fluff]

What code do you currently have?

 

[gosuansa]

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

 

[Fluff]

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.

 

[My Aswer Is This]

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:

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

 

[gosuansa]

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?