Select choosen rows

gosuansa

New Member
Joined
Jul 24, 2015
Messages
7
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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.
 
Upvote 0
How will the macro know which accounts you want to select & where do you want to add the new row?
 
Upvote 0
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
 
Upvote 0
What code do you currently have?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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