Select two consecutive rows

  • Thread starter Thread starter Bruce Roberson
  • Start date Start date
B

Bruce Roberson

I know that if I want to select for example a whole column
of contiguous data, that I can select in this manner:

dataRange(Selection, Selection.End(xlDown)).Select
__________________________________________________________
However, if I only want to start with selecting two cells,
then I ought to be able to issue a statement like this one
to select the active cell, plus one additional cell right
below. But this doesn't work. Any ideas how to change it?

Range(Selection, Offset(1, 0)).Select
___________________________________________________________

Also, someone has gone to sleep today on the posting of
newsgroups on the Microsoft web site where I normally can
get these responses pretty timely. And google always stays
behind. So, it may be a while before I get your post if
you post back a response today. I don't get to use my
favorite method of access newsgroups, Outlook Express when
I'm at work.

Bruce
 
Activecell.Resize(2,1).Select

Range(ActiveCell,ActiveCell.Offset(1,0)).Select

Regards,
Tom Ogilvy
 
Tom:

Actually what I'm running into is this:

I'm sitting in Cell A10 which is the left most corner of an area I need
to highlight for an advanced query setup with fields. I got there with
the command below since I had been in Cell B10 when I made my copy of
the range type2header:

ActiveCell.Offset(0, -1).Select

From Cell A10, I have contiguous data beginning in cells A11 and in Cell
B10. From there it is contiguous down through lets say to cell S325. I
also have a complication in that Cells B326 through H326 should not be
highlighted as they are a different data type and not needed for the
advanced query operation. Now these are not finite row numbers; they
will always vary each month as to how far down it goes.

Ordinarily I might have done a selection of the current region, but I
guess you can see that won't work in this case. If I try and go end down
end right with the commands below, it does not work to make the
selection I need.

Range(ActiveCell, ActiveCell.End(xlDown)).Select
Range(ActiveCell, ActiveCell.End(xlToRight)).Select

So, I'm stuck at this point on how to highlight what I need.
Listed below is my full routine so far.
____________________________________________________________

Sub Copytype2hdr()
Range("startexportcell").Offset(1, 0).Select 'Selects B9
Selection.EntireRow.Insert 'Inserting one row
Range("Type2header").Copy 'header for adv query
ActiveCell.PasteSpecial xlPasteValues 'paste header in B10
Application.CutCopyMode = False
ActiveCell.Offset(0, -1).Select move cursor to A10
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
End Sub
 
I'll try this in the morning when I get to work, but is this going to work
if the active cell is blank, and the cell to the right and the cell below
are not blank?

Bruce
 
Tom:
In your post yesterday, you listed this as a way to do the highlighting.


Since Cell A10 is the left most cell and since it is blank, this line of
code only highlighted Cells A10..B11 because of the starting cell. Is
there a way to first select this cell and then still do an end down, end
right type code?Again it appears you only get two select command lines
in a row and then the thing resets itself for some reason.

I think the whole problem is that the left most cell in the range is
where the focus of the end to right and the end down type commands move
from.

Bruce
 
Ok, I got the full routine done. I had to give the range "type2header" a
dummy entry in Column A, so that when it was copied it would take the
selection that Tom had given me yesterday.

"Range(ActiveCell.End(xlDown), ActiveCell.End _(xlToRight)).Select"

Other than that workaround, it was just a matter of hiding the zero
lines in the data with the advanced filter so I could copy what was left
over to a new spreadsheet and then save that in the CSV format required
by the state for the upload to their system. *****Piece of Cake
Right?**** <vbg>

There is one thing I'd rather do that I haven't tried yet. I'd rather
the csv file name be named in part by the spreadhsheet range value found
in range PRMO. So, rather than:

ActiveWorkbook.SaveAs Filename:= _
"D:\MyFiles\Data\nmtax\NMTAX.CSV", etc etc

I'd rather it be:

ActiveWorkbook.SaveAs Filename:= _
"D:\MyFiles\Data\nmtax\"Prmo"&NMTAX.CSV",

notice the "prmo"& added in there. Is there a way to make that sort of
thing work here?

My complete procedure is listed below for your review:


Sub Taxcsvfile()
Range("startexportcell").Offset(1, -1).Select
Selection.EntireRow.Insert
Range("Type2header").Copy
ActiveCell.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range(ActiveCell.End(xlDown), ActiveCell.End(xlToRight)).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("ExportCriti"), Unique:=False
Range("StartExportCell").Offset(1, 0).Select
Selection.EntireRow.Delete
Range("StartExportCell").Select
Range(ActiveCell.End(xlDown), ActiveCell.Offset(0, 20)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"D:\MyFiles\Data\nmtax\NMTAX.CSV", FileFormat:=xlCSV _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Sheets("ExportTax").ShowAllData
Range("A1").Select
Application.DisplayAlerts = True
End Sub
 
Range(ActiveCell.Offset(1,0).End(xlDown), _
ActiveCell.Offset(0,1).End(xlToRight)).Select

Regards,
Tom Ogilvy
 
Tom:

You never cease to amaze. Your solutions are always right on as long as
I can define the problem properly for you.
I thought that blank cell that I was starting in would be a problem.


Thanks again,


Bruce
 
Back
Top