loop?

[scaredycat]

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!

 

[Bob Phillips]

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

 

[scaredycat]

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

 

[Susan]

typo.......

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


susan

 

[Bob Phillips]

Typo on my part, try

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

 

[Bob Phillips]

You missed the other one

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


--
__________________________________
HTH

Bob

typo.......

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


susan

 

[Susan]

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

 

[scaredycat]

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?

 

[Dave Peterson]

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

Or

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

 

[scaredycat]

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

 

[scaredycat]

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

 

[Bob Phillips]

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

 

[scaredycat]

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