Can a macro write a macor

P

PCOR

I would love to have a macro do the following.
I would place my cursor at the top of a col and then press a button.
Pressing that button would do one of two things. Show all the non blank
entries in that col or if the non entries were showiong,would show all the
entries in the selected col.
Can this be done
I would appreciate lots of help.
Thanks
 
P

PCOR

I have a number of col and rows. Every row of every col does not necessarily
contain data
Example:
A B C D
1 12 9 45
2 3 54
3 22 78
4 112
5 100 14
6 25 65

Using the example above: If I placed my cursor at A1 and click a button I
want to see only rows1,2,5
If I was to press the button again(with the cursor at A1), then I would see
the entire col again.
I would like this process to continue as I add more data to col E,F etc
Thanks in advance


Vasant Nanavati said:
What do you mean by "show"? I assume they are already showing.
Click to expand...
 
V

Vasant Nanavati

Data | Filter | AutoFilter

should do what you want.

Not sure what this has to do with a macro writing a macro! :)

--

Vasant


PCOR said:
I have a number of col and rows. Every row of every col does not necessarily
contain data
Example:
A B C D
1 12 9 45
2 3 54
3 22 78
4 112
5 100 14
6 25 65

Using the example above: If I placed my cursor at A1 and click a button I
want to see only rows1,2,5
If I was to press the button again(with the cursor at A1), then I would see
the entire col again.
I would like this process to continue as I add more data to col E,F etc
Thanks in advance
Click to expand...
 
R

Rob van Gelder

Intersect(Cells.SpecialCells(xlCellTypeBlanks),
ActiveCell.EntireColumn).EntireRow.Hidden = True

--
Rob van Gelder - http://www.vangelder.co.nz/excel


PCOR said:
I have a number of col and rows. Every row of every col does not necessarily
contain data
Example:
A B C D
1 12 9 45
2 3 54
3 22 78
4 112
5 100 14
6 25 65

Using the example above: If I placed my cursor at A1 and click a button I
want to see only rows1,2,5
If I was to press the button again(with the cursor at A1), then I would see
the entire col again.
I would like this process to continue as I add more data to col E,F etc
Thanks in advance
Click to expand...
 
J

jason

this seems to work:

Private Sub CommandButton1_Click()
call filterxxx (1)
End Sub

Private Function filterxxx(i As Integer)
Application.ScreenUpdating = False

If ActiveSheet.AutoFilterMode = False Then
Range("DATABASE").Select
With Selection
.AutoFilter Field:=i, Criteria1:="<>"
End With
Else
ActiveSheet.AutoFilterMode = False
End If

Application.ScreenUpdating = True
End Function

this is assuming that the table of numbers is a named range called
"DATABASE".
The code for commandbutton1 will filter column 1 of DATABASE.You could
add another commandbutton at the top of column 2 and simply have:

Private Sub CommandButton2_Click()
call filterxxx (2)
End Sub

it should call the function but will filter using column2

Hope this is some help
Jason


PCOR said:
I have a number of col and rows. Every row of every col does not necessarily
contain data
Example:
A B C D
1 12 9 45
2 3 54
3 22 78
4 112
5 100 14
6 25 65

Using the example above: If I placed my cursor at A1 and click a button I
want to see only rows1,2,5
If I was to press the button again(with the cursor at A1), then I would see
the entire col again.
I would like this process to continue as I add more data to col E,F etc
Thanks in advance
Click to expand...
 
P

PCOR

Thanks for the efort Jason
How would you chnage this code to NOT have to name the col "DATABASE" but to
refer to the SELECTED COL
Thanks
 
Top