variable use in range

[Dave Peterson]

I tried this:

I put put headers in row 1.
I merged A2:A4, A5:A7, A8:10, A11:A13, A14:A16
I left B2:B16 unmerged.

I used this as my test data:

Row# ColA ColB
1 a c
2 aa 1
3 2
4 3
5 bb 4
6 2
7 3
8 aa 7
9 2
10 9
11 dd 4
12 5
13 6
14 ee 7
15 8
16 9

Since you showed the middle row of the merged area as visible, I figured that
you filtered on a column that wasn't merged. (When I filtered on column A, I
saw the toprow of the merged area.)

I filtered column B to show just the 2's:

I saw this:

Row# ColA ColB
1 a c
3 aa 2
6 bb 2
9 aa 2

The middle row of the merged area showed up in the filter.

I ran this code:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Cells.Count < 2 Then
MsgBox "not enough visible cells"
Exit Sub
Else
Set myRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
myRng.Cells(1).Select
MsgBox myRng.Cells(1).Address
End If
End With

End Sub

I got $A$3 back in the message box and $a$3 was selected in the worksheet.

It seemed to work ok for me in xl2002.

So what version of excel are you using--merged cells have become easier to work
with in later versions of excel since they were introduced in xl97.

And if I misunderstood your data layout, maybe you could go into more
specifics. I don't think I can offer anything more without more info.

Only the rows are merged. E.g 631 to 633, 634 to 636, 637 to 639
F G H

 

[crapit]

If the column is at "F", do I just need to change the in bold?

 

[Dave Peterson]

If your autofilter range starts in column A, then column F will be the 6th
column over.

But I'm not sure that's the fix.

crapit wrote:

If the column is at "F", do I just need to change the in bold?

 

[crapit]

OK that fix it. But I cant seem to unhide the merge cell from rows!

 

[Dave Peterson]

Maybe you can dump the mergedcells and use this idea:

Put the same value in each of the cells in what would have been that
mergedarea. But hide the duplicated values using conditional formatting.

Debra Dalgleish has some nice techniques to fill columns with the value of the
cell above at:
http://www.contextures.com/xlDataEntry02.html

And she shows how to use conditional formatting to "hide" these duplicated
values at:
http://www.contextures.com/xlCondFormat03.html#Duplicate

OK that fix it. But I cant seem to unhide the merge cell from rows!

 

[crapit]

I cant dump that merge cell. That spreadsheet is meant to be shared! As you
can see that the number of row have gone up to 600+, I cant possibly
modified every merge cell. As I need certain column and row of data, I
therefore wanted a filtered spreadsheet that is just relevant to me!!!

BTW, how do I make the macro permanently available w/o importing from the VB
editor each time I load the spreadsheet?

 

[Dave Peterson]

Removing the merged cells is pretty easy.

Just select the range (all the cells), then format|cells|alignment tab and
remove the checkmark from the Merged cell box.

Then use both those techniques at Debra's site and you're done.

I would guess that it would take less than 5 minutes.

Store the macro in the workbook's project. After you've imported it into this
workbook, save the workbook and the macro will be there next time.

I cant dump that merge cell. That spreadsheet is meant to be shared! As you
can see that the number of row have gone up to 600+, I cant possibly
modified every merge cell. As I need certain column and row of data, I
therefore wanted a filtered spreadsheet that is just relevant to me!!!

BTW, how do I make the macro permanently available w/o importing from the VB
editor each time I load the spreadsheet?

 

[crapit]

But the problem is the workbook is daily updated. To save any changes, I
need to save the file to the local hard disk 1st.

As for the copy to worksheet command, how to set it to copy values and
number format only w/o condtional formatting, ?

..Cells(x, 6).copy _
Destination:=Worksheets(shtName).Cells(y, 1)

 

[crapit]

How to find out the column at the end of the region that contains the source
range.?

 

[Dave Peterson]

If you save the workbook anywhere, all macros that you added will be saved in
that workbook, too.

I think I'd just assign the value and numberformat:

dim fCell as range
dim tCell as range

'whatever x,y are???
set fcell = worksheets("sheet1").cells(x,6)
set tCell = worksheets(shtName).cells(y,1)

tcell.value = fcell.value
tcell.numberformat = fcell.numberformat

But the problem is the workbook is daily updated. To save any changes, I
need to save the file to the local hard disk 1st.

As for the copy to worksheet command, how to set it to copy values and
number format only w/o condtional formatting, ?

.Cells(x, 6).copy _
Destination:=Worksheets(shtName).Cells(y, 1)

 

[Dave Peterson]

If the source range is contiguous, you can use:

dim myRng as range
dim LastCol as long
set myRng = .range("g1:L99") 'however you set it

with myrng
lastcol = .cells(.cells.count).column
end with

msgbox lastcol

It just finds the lastcell and uses the column that that one is in.

=====
Alternatively, I like to find a row that must contain data (headers????):

dim Lastcol as long
with worksheets("sheet1")
lastcol = .cells(1,.columns.count).end(xltoleft).column
end with

(I used row 1 for my row that always has data.)

How to find out the column at the end of the region that contains the source
range.?

 

[crapit]

Which means I need to load the macro everytime I open the file?

where to use the tcell.value and tcell.numberformat ?

 

[crapit]

If the range is not a fixed value?

If the source range is contiguous, you can use:

dim myRng as range
dim LastCol as long
set myRng = .range("g1:L99") 'however you set it

with myrng
lastcol = .cells(.cells.count).column
end with

msgbox lastcol

It just finds the lastcell and uses the column that that one is in.

=====
Alternatively, I like to find a row that must contain data (headers????):

dim Lastcol as long
with worksheets("sheet1")
lastcol = .cells(1,.columns.count).end(xltoleft).column
end with

(I used row 1 for my row that always has data.)

 

[Dave Peterson]

If you save the workbook with the macro, then the next time you open it, it'll
have the macro.

 

[Dave Peterson]

That was an example. As long as myRng is a real range, then the code won't
change.

If the range is not a fixed value?

 

[crapit]

I used
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row to find out the
last row of contiguous data
but
lastcol = ActiveSheet.Cells(columns.Count, 1).End(xltoright).Row to
find out the last column of contiguous data didnt work! WHY?

 

[crapit]

Wat I mean is that I need to get the workbook from the shared drive
everyday!

 

[Dave Peterson]

The workbook is refreshed (built from scratch) every day???

If yes, then put the macro in another workbook. And run it from there!

 

[Dave Peterson]

lastcol = ActiveSheet.Cells(columns.Count, 1).End(xltoright).Row
is the same thing as:
lastcol = ActiveSheet.Cells(256, "A").End(xltoright).Row
which will always be on row 256--no matter how far to the right you go.

lastcol = ActiveSheet.Cells(1, columns.Count).End(xltoright).column

will give the last column in Row 1 with something in it.

 

[crapit]

But I still get 256 for lastcol.

I try to set the font size and making it AUTOFIT but the .selection give
error

With Worksheets("status")
.Cells.font.Name = "Arial"
.Cells.font.Size = 10
.Cells.font.Strikethrough = False
.Selection.Columns.AutoFit
End with