MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

I'm stuck . . . can you help, please?

  Asked By: Lucille    Date: Aug 25    Category: MS Office    Views: 717

I'm stuck on something that is probably basic for all of you--
because of a family crisis, I'm away from all of My reference
materials, and I haven't been able to find the answer online. I'm
new to working with Visual Basic, but have done some other kinds of

So, here's the situation:

I'm trying to write an Excel macro in Visual Basic (using Excel
2003 -- on Windows XP) and I can get it to work pretty well for
almost everything except:

The data is in mulitple columns/rows and has a rather high
duplicate entry problem . . . (the last sort after deleting
duplicates reduced the number of entry rows from 545 to 141). Then
the error checking that I was able to set up does a fine job of
doing the final sort, which has to be checked by a supervisor. That
means, of course, that I need to set up a simple way for them to
reinput the sort formulas. . . . so, I have been trying, without
success to write a macro which would count the rows and (the count
part I can do, no prob) and select and activate the relevant block
of cells to fill down the formula's again, so it updates the
formulas after a deletion.

I can get it to so everything except this: . . . to select the
block of relevant cells from a the variables that the count formulas
produce. So, I might need to have the macro select "E2:O141" and
do the fill down, or next set of data, might need to
select "E2:O145" or whatever, given the data that wasn't deleted
from the duplication sort. . . . I can activate "E2" and I can
activate "O141" (or whatever), but I can't seem to get the syntax
right to select that entire block from the other formula variables
(i.e. "E2:O141" in the example.

I appreciate anyone who can give me a hand on this . . . I know
I could do it in the other programing that I've done, but don't
think I've got it in Visual Basic.



5 Answers Found

Answer #1    Answered By: Adalricus Fischer     Answered On: Aug 25

If the information is contiguous and you have empty cells around the
final sort  grouping, you could use the CurrentRegion property. This
selects the group of cells around the selected cell up to the first
empty row and first empty column. Search for CurrentRegion Property
in VBA Help.

Answer #2    Answered By: Ada Bailey     Answered On: Aug 25

Thank you for your suggestion, but in fact that doesn't fit the
grouping of the relevant cells. They are all contiguous, but not
surrounded by emply cells--so, in effect, it is only one part of a
larger block. In the present data  set, for example the entire block
would go from A1:N141, but the needed selection for the formulas is
E2:N141 . . . and the next set of data might have a different number
of rows, so I have a counter set up to check. I can get the correct
address for the block to show up in a MSG box, using the variables
from the counter, but I can't figure out the syntax to put that in
as a range to select for the fill down. There may be another way to
do this entirely, so I'm open to suggestions. Since I'm new to
Visual Basic, I might be overlooking something very simple.

Answer #3    Answered By: Martha Gonzalez     Answered On: Aug 25

If you can display the range you want to select in a MsgBox, you are almost
Here is a sub with 2 examples of displaying a range in a Msgbox, then
selecting that range.

'Declare variables
Dim Row1 As Long, Row2 As Long, Row3 As Long, Row4 As Long
Dim Col1 As Integer, Col2 As Integer, Col3 As String, Col4 As String
'Assign values to variables
Row1& = 1
Row2& = 21
Row3& = 11
Row4& = 31
Col1% = 2
Col2% = 7
Col3$ = "E"
Col4$ = "I"
'Select using numerical column references
MsgBox "Range(Cells(" & Row1& & "," & Col1% & "), Cells(" & Row2& & "," &
Col2% & "))"
Range(Cells(Row1&, Col1%), Cells(Row2&, Col2%)).Select
'Select using alphabetical column references
MsgBox "Range(" & Col3$ & Row3& & ":" & Col4$ & Row4& & ")"
Range(Col3$ & Row3& & ":" & Col4$ & Row4&).Select
End Sub

Answer #4    Answered By: Poppy Brown     Answered On: Aug 25

YES!!! . . . THANKS YOU! at least My initial attempt to apply this
sub with my variables worked fine. I'll have to test it out on
several to be sure, but I think I understand what I was missing

Answer #5    Answered By: Juanita Mason     Answered On: Aug 25

Set a variable to get the address of the deleted row (strStart). Set
another variable to get the end of the selection (strEnd). The set
your range using the two variables as the start and end points.

I just tested the example below.

Sub SortRange
Dim strStart As String
Dim strEnd As String
Dim strSheet As String

strSheet = ActiveSheet.Name

strStart = ActiveCell.Address

strEnd = ActiveCell.Offset(20, 5).Address

Worksheets(strSheet).Range(strStart, strEnd).Sort key1:=Worksheets
(strSheet).Range(strStart), _

End Sub

Didn't find what you were looking for? Find more on I'm stuck . . . can you help, please? Or get search suggestion and latest updates.