please anyone can help on:
1.
Write a macro to obtain all the below names within single quotes &
separated by comma in one cell. The data can have any number of rows.
Define Dynamic range

green
blue
yellow

i.e,
in a single cell as
'green', 'blue', 'yellow'

2.
Write a macro which fills the blanks with the respective Course Name
Ids in Column A

ie.
1234 white
blue
yellow
4568 green
red


fill all the blanks column with the respective number

please help

You didn't tell us starting cells or destination cells, so I had to guess;
change the StartCell, Destination and ColLetter to match your actual
situation. The CombineCells macro answer your first question and the
FillInTheBlanks macro answers your second question.

Sub CombineCells()
Dim X As Long, LastRow As Long, StartCell As Range, Destination As Range
Set StartCell = Range("A2")
Set Destination = Range("B2")
LastRow = Cells(Rows.Count, StartCell.Column).End(xlUp).Row
Destination.Value2 = "''" & Join(WorksheetFunction.Transpose( _
Range(StartCell, Cells(LastRow, _
StartCell.Column))), "', '") & "'"
End Sub

Sub FillInTheBlanks()
Dim Area As Range, LastRow As Long
Const ColLetter As String = "A"
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
For Each Area In Columns(ColLetter)(1).Resize(LastRow). _
SpecialCells(xlCellTypeBlanks).Areas
Area.Value = Area(1).Offset(-1).Value
Next
End Sub

I couldn't decide if your questions were a homework assignment or not. Just
so you know... if it is a homework assignment, then I can **absolutely
guarantee** your instructor will know you didn't write the above code.

--
Rick (MVP - Excel)


"Ratheesh" <(E-Mail Removed)> wrote in message
news:a0b003d5-14de-46a9-9cb8-(E-Mail Removed)...
> please anyone can help on:
> 1.
> Write a macro to obtain all the below names within single quotes &
> separated by comma in one cell. The data can have any number of rows.
> Define Dynamic range
>
> green
> blue
> yellow
>
> i.e,
> in a single cell as
> 'green', 'blue', 'yellow'
>
> 2.
> Write a macro which fills the blanks with the respective Course Name
> Ids in Column A
>
> ie.
> 1234 white
> blue
> yellow
> 4568 green
> red
>
>
> fill all the blanks column with the respective number
>
> please help

Homework?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Ratheesh" <(E-Mail Removed)> wrote in message
news:a0b003d5-14de-46a9-9cb8-(E-Mail Removed)...
> please anyone can help on:
> 1.
> Write a macro to obtain all the below names within single quotes &
> separated by comma in one cell. The data can have any number of rows.
> Define Dynamic range
>
> green
> blue
> yellow
>
> i.e,
> in a single cell as
> 'green', 'blue', 'yellow'
>
> 2.
> Write a macro which fills the blanks with the respective Course Name
> Ids in Column A
>
> ie.
> 1234 white
> blue
> yellow
> 4568 green
> red
>
>
> fill all the blanks column with the respective number
>
> please help

On Nov 27, 6:50*pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> Homework?
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"Ratheesh" <ratheesh.ramachandh...@gmail.com> wrote in message
>
> news:a0b003d5-14de-46a9-9cb8-(E-Mail Removed)...
>
>
>
> > please anyone can help on:
> > 1.
> > Write a macro to obtain all the below names within single quotes &
> > separated by comma in one cell. The data can have any number of rows.
> > Define Dynamic range
>
> > green
> > blue
> > yellow
>
> > i.e,
> > in a single cell as
> > 'green', 'blue', 'yellow'
>
> > 2.
> > Write a macro which fills the blanks with the respective Course Name
> > Ids in Column A
>
> > ie.
> > 1234 white
> > * * * *blue
> > * * * *yellow
> > 4568 green
> > * * * *red
>
> > fill all the blanks column with the respective number
>
> > please help- Hide quoted text -
>
> - Show quoted text -

Hi -

Actually I am a bigginer in VBA, but you are right here, I was having
2 test and it should complete within 30 minutes ! I was really
worried, whether I can complete or not ?
BUT NOW I AM SO HAPPY.... I COMPLETED WITHIN 30 MINUTES WITH MY OWN
CODING !!!
If I need to paste 1 number to all the cell then it would be easy, but
here I need to paste the value for corresponding value to the below
blank cells eg:,
A B
2323 BLUE
YELLLOW
RED
4569 GREEN
ORANGE here, 2323 for Yellow and Red (A Column) & 4569 for
Orange, here it has to stop...

But as per my coding, it will continue till the end of the column,
thats why I restricted in Range("A1:A33"), I tried to take the
rows.count for B column, but I couldnt complete within the time, so I
used the range !!!
In this .FillDown I got, when I recorded for Ctrl + D !

Sub fillblanks()
For Each x In Range("A1:A33")
If x = "" Then x.FillDown
Next x
End Sub


This is for my first test, this came in my mind immediately, coz, I
was doing egs For Each loop many times last night !

Sub appendline()
For Each x In Range("A:A")
If x <> "" Then
Cells(3, 2).Value = "'" & Cells(3, 2).Value & x & "'," ' end
with a comma - again one code to remove that !
End If
Next x
End Sub



Cheeeeeeers
Ratheesh