loop?
 

can you help me
i'm trying to create a macro that will loop through all the data in a user
specified column/row and return on a new sheet all non-blank data from that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!

loop?
 

With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in message
...
can you help me
i'm trying to create a macro that will loop through all the data in a user
specified column/row and return on a new sheet all non-blank data from
that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!

loop?
 

Thanks Bob but excel has a prblem with this it has a message that says "sub
or function not defined" for the word 'Column' in this line

Worksheets("Sheet1")Column(1).Copy .Range("A1")

I'm probably being really basic but how do I fix this?

many thanks



"Bob Phillips" wrote:

With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in message
...
can you help me
i'm trying to create a macro that will loop through all the data in a user
specified column/row and return on a new sheet all non-blank data from
that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!

loop?
 

typo.......

Worksheets("Sheet1")Column(1).Copy .Range("A1")
should be
Worksheets("Sheet1").Column(1).Copy.Range("A1")

:)
susan


On Jun 24, 3:02*pm, "Bob Phillips" wrote:
* * With Worksheets("Sheet2")

* * * * Worksheets("Sheet1")Column(1).Copy .Range("A1")
* * * * LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
* * * * For i = LastRow To 2 Step -1

* * * * * * If .Cells(i, "A").Value = "" Then

* * * * * * * * .Rows(i).Delete
* * * * * * End If
* * * * Next i
* * End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in message

...



can you help me
i'm trying to create a macro that will loop through all the data in a user
specified column/row and return on a new sheet all non-blank data from
that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!- Hide quoted text -

- Show quoted text -

loop?
 

Typo on my part, try

Worksheets("Sheet1").Columns(1).Copy .Range("A1")

--
__________________________________
HTH

Bob

"scaredycat" wrote in message
...
Thanks Bob but excel has a prblem with this it has a message that says
"sub
or function not defined" for the word 'Column' in this line

Worksheets("Sheet1")Column(1).Copy .Range("A1")

I'm probably being really basic but how do I fix this?

many thanks



"Bob Phillips" wrote:

With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in message
...
can you help me
i'm trying to create a macro that will loop through all the data in a
user
specified column/row and return on a new sheet all non-blank data from
that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!

loop?
 

You missed the other one

Worksheets("Sheet1").Columns(1).Copy.Range("A1")


--
__________________________________
HTH

Bob

"Susan" wrote in message
...
typo.......

Worksheets("Sheet1")Column(1).Copy .Range("A1")
should be
Worksheets("Sheet1").Column(1).Copy.Range("A1")

:)
susan


On Jun 24, 3:02 pm, "Bob Phillips" wrote:
With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in message

...



can you help me
i'm trying to create a macro that will loop through all the data in a
user
specified column/row and return on a new sheet all non-blank data from
that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!- Hide quoted text -

- Show quoted text -

loop?
 

yep, i got the & the space, but missed the s on the columns.......
LOL
susan


On Jun 24, 3:25*pm, "Bob Phillips" wrote:
You missed the other one

Worksheets("Sheet1").Columns(1).Copy.Range("A1")

--
__________________________________
HTH

Bob

"Susan" wrote in message

...
typo.......

Worksheets("Sheet1")Column(1).Copy .Range("A1")
should be
Worksheets("Sheet1").Column(1).Copy.Range("A1")

:)
susan

On Jun 24, 3:02 pm, "Bob Phillips" wrote:



With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in message

...

can you help me
i'm trying to create a macro that will loop through all the data in a
user
specified column/row and return on a new sheet all non-blank data from
that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

loop?
 

Sorry guys... it still doesn't like that line i've now got a message saying
"runtime error 424 object required" what does that mean?

"Bob Phillips" wrote:

You missed the other one

Worksheets("Sheet1").Columns(1).Copy.Range("A1")


--
__________________________________
HTH

Bob

"Susan" wrote in message
...
typo.......

Worksheets("Sheet1")Column(1).Copy .Range("A1")
should be
Worksheets("Sheet1").Column(1).Copy.Range("A1")

:)
susan


On Jun 24, 3:02 pm, "Bob Phillips" wrote:
With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in message

...



can you help me
i'm trying to create a macro that will loop through all the data in a
user
specified column/row and return on a new sheet all non-blank data from
that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!- Hide quoted text -

- Show quoted text -

loop?
 

Worksheets("Sheet1").Columns(1).Copy _
destination:=.Range("A1")

Or

Worksheets("Sheet1").Columns(1).Copy .Range("A1") '<--note the spaces!

scaredycat wrote:

Sorry guys... it still doesn't like that line i've now got a message saying
"runtime error 424 object required" what does that mean?

"Bob Phillips" wrote:

You missed the other one

Worksheets("Sheet1").Columns(1).Copy.Range("A1")


--
__________________________________
HTH

Bob

"Susan" wrote in message
...
typo.......

Worksheets("Sheet1")Column(1).Copy .Range("A1")
should be
Worksheets("Sheet1").Column(1).Copy.Range("A1")

:)
susan


On Jun 24, 3:02 pm, "Bob Phillips" wrote:
With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in message

...



can you help me
i'm trying to create a macro that will loop through all the data in a
user
specified column/row and return on a new sheet all non-blank data from
that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!- Hide quoted text -

- Show quoted text -




--

Dave Peterson

loop?
 

Thanks guys for all your help, but I still can't get it to work, what object
do I need to define....i'm sorry to bother you further but thank you so much
for your help so far

Thanks :)

"Susan" wrote:

yep, i got the & the space, but missed the s on the columns.......
LOL
susan


On Jun 24, 3:25 pm, "Bob Phillips" wrote:
You missed the other one

Worksheets("Sheet1").Columns(1).Copy.Range("A1")

--
__________________________________
HTH

Bob

"Susan" wrote in message

...
typo.......

Worksheets("Sheet1")Column(1).Copy .Range("A1")
should be
Worksheets("Sheet1").Column(1).Copy.Range("A1")

:)
susan

On Jun 24, 3:02 pm, "Bob Phillips" wrote:



With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in message

...

can you help me
i'm trying to create a macro that will loop through all the data in a
user
specified column/row and return on a new sheet all non-blank data from
that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

loop?
 

Sorry I just got the answer to that last bit and the good news is it
works...yay:), the only thing is though it only specifies the column headers
and row names it doesn't filter the data in those columns/rows by non-blank
values and remove the unnecessary ones.

I'm not very good at explaining things sorry, so for examples sake if a user
clicks on a row name I want to copy to a seperate sheet all the non-blank
columns and values for that row, and likewise for selecting a column i want
to filter that column by all the non-blank values and copy those values with
the row names (so that the data is meaningful) to a seperate sheet.

Does this make any sense, i'm sorry to take up your time.

Yours gratefully

scaredycat :)
"scaredycat" wrote:

Thanks guys for all your help, but I still can't get it to work, what object
do I need to define....i'm sorry to bother you further but thank you so much
for your help so far

Thanks :)

"Susan" wrote:

yep, i got the & the space, but missed the s on the columns.......
LOL
susan


On Jun 24, 3:25 pm, "Bob Phillips" wrote:
You missed the other one

Worksheets("Sheet1").Columns(1).Copy.Range("A1")

--
__________________________________
HTH

Bob

"Susan" wrote in message

...
typo.......

Worksheets("Sheet1")Column(1).Copy .Range("A1")
should be
Worksheets("Sheet1").Column(1).Copy.Range("A1")

:)
susan

On Jun 24, 3:02 pm, "Bob Phillips" wrote:



With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in message

...

can you help me
i'm trying to create a macro that will loop through all the data in a
user
specified column/row and return on a new sheet all non-blank data from
that
selected colum or row with the relevant row/column headers as appear on
original sheet.

I'm a bit new to this sorry!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

loop?
 

Isn't that just what it does, copy column A, without the blanks?

--
__________________________________
HTH

Bob

"scaredycat" wrote in message
...

Sorry I just got the answer to that last bit and the good news is it
works...yay:), the only thing is though it only specifies the column
headers
and row names it doesn't filter the data in those columns/rows by
non-blank
values and remove the unnecessary ones.

I'm not very good at explaining things sorry, so for examples sake if a
user
clicks on a row name I want to copy to a seperate sheet all the non-blank
columns and values for that row, and likewise for selecting a column i
want
to filter that column by all the non-blank values and copy those values
with
the row names (so that the data is meaningful) to a seperate sheet.

Does this make any sense, i'm sorry to take up your time.

Yours gratefully

scaredycat :)
"scaredycat" wrote:

Thanks guys for all your help, but I still can't get it to work, what
object
do I need to define....i'm sorry to bother you further but thank you so
much
for your help so far

Thanks :)

"Susan" wrote:

yep, i got the & the space, but missed the s on the columns.......
LOL
susan


On Jun 24, 3:25 pm, "Bob Phillips" wrote:
You missed the other one

Worksheets("Sheet1").Columns(1).Copy.Range("A1")

--
__________________________________
HTH

Bob

"Susan" wrote in message

...
typo.......

Worksheets("Sheet1")Column(1).Copy .Range("A1")
should be
Worksheets("Sheet1").Column(1).Copy.Range("A1")

:)
susan

On Jun 24, 3:02 pm, "Bob Phillips" wrote:



With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in
message

...

can you help me
i'm trying to create a macro that will loop through all the data
in a
user
specified column/row and return on a new sheet all non-blank data
from
that
selected colum or row with the relevant row/column headers as
appear on
original sheet.

I'm a bit new to this sorry!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

loop?
 

It does that you're right but what I want it to do is as follows (sorry i'm
not so good with the words!)

Column A contains names of students, The other column headers are exams, the
data in the table are the dates that the students passed particular exams,
what i want to do is select an exam e.g. exam 1 and produce on a different
sheet the names of the students that have taken that exam and the respective
dates from that column...do you see?

Also I want to be able to select a students name and produce on a different
sheet the names of the exams that they have passed and the dates. but I
don't want to show the column headers (e.g. exam names for the exams not yet
passed)

Is that clearer, i'm sorry I'm rubbish at explaining but i'm learning a lot :)

thank you
"Bob Phillips" wrote:

Isn't that just what it does, copy column A, without the blanks?

--
__________________________________
HTH

Bob

"scaredycat" wrote in message
...

Sorry I just got the answer to that last bit and the good news is it
works...yay:), the only thing is though it only specifies the column
headers
and row names it doesn't filter the data in those columns/rows by
non-blank
values and remove the unnecessary ones.

I'm not very good at explaining things sorry, so for examples sake if a
user
clicks on a row name I want to copy to a seperate sheet all the non-blank
columns and values for that row, and likewise for selecting a column i
want
to filter that column by all the non-blank values and copy those values
with
the row names (so that the data is meaningful) to a seperate sheet.

Does this make any sense, i'm sorry to take up your time.

Yours gratefully

scaredycat :)
"scaredycat" wrote:

Thanks guys for all your help, but I still can't get it to work, what
object
do I need to define....i'm sorry to bother you further but thank you so
much
for your help so far

Thanks :)

"Susan" wrote:

yep, i got the & the space, but missed the s on the columns.......
LOL
susan


On Jun 24, 3:25 pm, "Bob Phillips" wrote:
You missed the other one

Worksheets("Sheet1").Columns(1).Copy.Range("A1")

--
__________________________________
HTH

Bob

"Susan" wrote in message

...
typo.......

Worksheets("Sheet1")Column(1).Copy .Range("A1")
should be
Worksheets("Sheet1").Column(1).Copy.Range("A1")

:)
susan

On Jun 24, 3:02 pm, "Bob Phillips" wrote:



With Worksheets("Sheet2")

Worksheets("Sheet1")Column(1).Copy .Range("A1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

--
__________________________________
HTH

Bob

"scaredycat" wrote in
message

...

can you help me
i'm trying to create a macro that will loop through all the data
in a
user
specified column/row and return on a new sheet all non-blank data
from
that
selected colum or row with the relevant row/column headers as
appear on
original sheet.

I'm a bit new to this sorry!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -