Macro to select only cells within the range that contain data

  • Thread starter Thread starter wojo
  • Start date Start date
W

wojo

Thanks to this board, I now can sort my text+numerical values the way I
would like, with Excel Ignoring all characters but the numbers.

Now, this data is in a column that contains 43 entries. In the future
there may be more entries, but my current macro will only select the 43
entries (I made the macro using the macro builder) and selected only
the cells with data. If I select the additional cells, the sort puts
the blanks at the top of the form and this is not acceptable. (We have
no one else at work that will be able to change the macro, if I'm not
there and our list increases)

I need a solution that selects all cells in the range and puts the
blanks at the bottom OR I need a macro that will only select the cells
with data in them.

Any ideas?

Jo
 
Something like this, that assumes the data in column B

Set rng = Range("B1").Resize(Cells(Rows.Count, "B").End(xlUp).Row)
rng.Sort key1:=Range("B1"), Header:=xlNo
 
Well you asked for some code that would sort a variable amount of data, so
you would put it in the code where you need a sort. I can't be more precise
than that with the information provided so far.
 
Here is some more information. Sorry I wasn't more specific before.

My Data is in a table that needs sorting.
The first data entry is in cell B3 and the last data is contained in
F47.
Currently there are 45 rows of information, with three 'blank' rows
for future use.
I need the sort to select B3 through F47, but only the cells that
contain data.

There is NO header column and the data looks like this..

B C D E F
3 JO [6 6 REMARKS
4 WO 12 12 REMARKS
5 XX 7 7 REMARKS
6
7

I need a macro that will select B3 through F5 (as they are the only
cells that contain data AND I need the sort to use column D as the
primary sort (ascending), followed by column C (ascending), then column
B (ascending).

Can this be done? I tried playing with the solution given above, but I
was unable to get it to work.

Thanks, Jo
 
Additionally, there is formulas in column D and F, so they aren't
exactly blank. The sort must select only the rows that contain data in
column B. I look forward to any help I can get.

I tried entering the above 'code', but I kept getting an error when my
code include the additional columns that must be included in the sort
(D, C, then B.)

JO
 
I sure hope someone can help.
Here is the sort code that needs to be modified to only select the rows
in Column B, that contain data.

Range("B3:F47").Select
Selection.Sort Key1:=Range("D3"), Order1:=xlAscending,
Key2:=Range("C3") _
, Order2:=xlAscending, Key3:=Range("B3"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortNormal,
DataOption3 _
:=xlSortNormal


JO
 
This might do it:

Option Explicit
Sub testme()

Dim myRng As Range
With ActiveSheet
Set myRng = .Range("b3:F" & .Cells(.Rows.Count, "B").End(xlUp).Row)
End With

With myRng
.Cells.Sort Key1:=.Columns(3), Order1:=xlAscending, _
Key2:=.Columns(2), Order2:=xlAscending, _
Key3:=.Columns(1), Order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End With

End Sub

(I changed the header to no--you said that there was data starting in row 3.)
 
Wow, thanks! The only thing I had to change, was Ascending to
Descending. For some reason, the smallest numbers (sorted) to the
bottom of my sheet rather than the top. Don't know why. Anyway, it
was a simple change to get the results that I wanted.

The data, is copied from another sheet, which is a linked sheet of
data. When I copied the data to this current sheet, empty cells
(pasted values) become 0's. (Options, show zero values is NOT
checked). Because of this, even though rows in Column B did not
contain visible data, the sort was selecting these rows.

To work around the problem, I made a macro that selects column B (and
C) finds all 0's and replaces them with 'nothing'. I run this macro,
followed by your macro and everything works like I want.

Thank you so much. I am so glad you saw my note. I've been working on
my program for days (I'm actually on vacation) and you have been a
tremendous help.

Jo
 
Will this same concept work, if I have other times that a macro selects
a range?
Using the above example of data, if I want to copy the data in B3
through F7, but only select the rows that have data in column B, can
this be done too? These cells would be then pasted (values) to another
sheet (via a macro).

This might actually help my Zero problem.

One last thing. I did get one other unexpected result with your/my
modified sort.
This is what happpened

AK* A/L 6 6
CG* 6 6
CF* 6 6
JW* 22* 22
JK* 22 22
RA* 16 16
PR* 16 16
KO* 15 15
PB* A/L 14 14
VT* 14 14
SH 14 14

Column C contains the following type entries

6
A/L 6
CHU 6
[6

The cells that contain Letters before the number are sorting above the
numbers or numbers with a bracket infront of the number.

The data in Column D is actually a formula =ExtractNumbers(C1) (I could
change this if I need to. I could paste values into this column, but
so far, it hasn't made a difference)

I would really like these to sort to the bottom of the 6's. The result
would be this..

6
[6
A/L 6
S/L 6

As you can see the third column D is sorting with the 22 before the 14.
I must be doing something wrong,
You have solved the original question, your solution absolutely
selected only the rows that contain data in Column B.

Hope I haven't confused you. I'm confused.

Jo
 
I think I'd just look at each cell in that range. If it ends with a 6, then
copy it to the new worksheet.

I _think_ this does what you asked:

Option Explicit
Sub testme2()

Dim myRng As Range
Dim myCell As Range
Dim destCell As Range

With ActiveSheet
Set myRng = .Range("b3:F" & .Cells(.Rows.Count, "B").End(xlUp).Row)
End With

For Each myCell In myRng.Columns(1).Cells
If IsEmpty(myCell.Value) Then
'do nothing
Else
If Right(myCell.Offset(0, 1), 1) = "6" Then
'do the copy
With Worksheets("sheet2")
Set destCell = .Cells(.Rows.Count, "B") _
.End(xlUp).Offset(1, 0)
End With

Intersect(myCell.EntireRow, myRng).Copy _
Destination:=destCell
End If
End If
Next myCell

End Sub


Will this same concept work, if I have other times that a macro selects
a range?
Using the above example of data, if I want to copy the data in B3
through F7, but only select the rows that have data in column B, can
this be done too? These cells would be then pasted (values) to another
sheet (via a macro).

This might actually help my Zero problem.

One last thing. I did get one other unexpected result with your/my
modified sort.
This is what happpened

AK* A/L 6 6
CG* 6 6
CF* 6 6
JW* 22* 22
JK* 22 22
RA* 16 16
PR* 16 16
KO* 15 15
PB* A/L 14 14
VT* 14 14
SH 14 14

Column C contains the following type entries

6
A/L 6
CHU 6
[6

The cells that contain Letters before the number are sorting above the
numbers or numbers with a bracket infront of the number.

The data in Column D is actually a formula =ExtractNumbers(C1) (I could
change this if I need to. I could paste values into this column, but
so far, it hasn't made a difference)

I would really like these to sort to the bottom of the 6's. The result
would be this..

6
[6
A/L 6
S/L 6

As you can see the third column D is sorting with the 22 before the 14.
I must be doing something wrong,
You have solved the original question, your solution absolutely
selected only the rows that contain data in Column B.

Hope I haven't confused you. I'm confused.

Jo
Click to expand...
 
Dave, I tried the new code, but I get a syntax error

Dim destCell



As Range '<<Syntax error here

With ActiveSheet
Set myRng = .Range("b3:F" & .Cells(.Rows.Count,
"B").End(xlUp).Row)
End With

I tried deleting the empty line etc and played with it, thinking it
might be the column width allowed here on the group, But I can't get it
to work.

Jo
 
That is really one line.

dim destcell as range

It looks like google is playing with the line spacing now.

If you spend any time at all working in the newsgroups, you may find it much
easier to connect directly to the MSServers.

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm
 
I couldn't find this note, using the newsgroups. If you want to mail
me the code, you can send it to
wojo36@(removeme)yahoo.com.

I've been playing with this all afternoon. Can't get the results that
I need.

Jo
 
Dave, I have successfully used your first response (with no changes)
and it works on my practice sheet. Now I will try to insert it into my
original macro. I think this might be when things mess up.

Here's the code I used on my practice sheet. It worked everytime.

Option Explicit
Sub testme()


Dim myRng As Range
With ActiveSheet
Set myRng = .Range("b3:F" & .Cells(.Rows.Count,
"B").End(xlUp).Row)
End With


With myRng
.Cells.Sort Key1:=.Columns(3), Order1:=xlAscending, _
Key2:=.Columns(2), Order2:=xlAscending, _
Key3:=.Columns(1), Order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbe­rs, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End With


End Sub


I will let you know if I have difficulty putting it into my original
macro. I need to do this, as my macro will delete the 0's that copied
into Column B.

Jo
 
Back
Top