Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Select a Range and one blank row above

  Asked By: Erica    Date: Sep 20    Category: MS Office    Views: 843
  


I'm trying to select a range from a larger range of data based on a cell value.
I'm sure there's an easier way to do this using a Do Loop or something similiar
but I'm not too familiar with that concept.



Here's my worksheet:

A B C D E F
1 Mike Blue 1
2 John Blue 2
3 Steve Green 1
4 Bill Green 1
5 Bob Red 2
6 Bob Red 3
7 Jan Red 4
8 Jan Red 5

I want to find the first instance of "red," insert a row between "red" and
"green", and then select all rows with "RED" INCLUDING the newly inserted row,
as a range.

I've got the following (mainly recorded) code. Bear with me as it is not very
elegant.

Range("b1").Select
Cells.Find(What:="RED").Activate
Selection.EntireRow.Insert
Range("b1").Select
Cells.Find(What:="RED").Activate
ActiveCell.Offset(-1, -1).Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select


My problem is that this code will select the blank line and the first row of
data but will NOT select to the end of the data. I even changed it to cell
"c2" and that didn't work.

Any thoughts?

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on Select a Range and one blank row above Or get search suggestion and latest updates.




Tagged: