Posts by Shbby

Understand and Thanks man

How to put the data into range somewhere i did not get your point.

I have a code which is working fine but it has corrupted the file i do not know why this thing is happening. I have attached a sample workbook.

If you could please help me to fix this i will appreciated the help.

Dim sheet As Worksheet
Dim RangeArray As Variant
Dim i As Long
Dim d As Object

Set sheet = Worksheets("Sheet1")

With sheet

RangeArray = .Range("A2:A50000").Value
End With

Set d = CreateObject("Scripting.Dictionary")

For i = LBound(RangeArray) To UBound(RangeArray)
d(RangeArray(i, 1)) = 1
Next i

With Worksheets("Sheet2").Range("C2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(d.Keys, ",")
.InCellDropdown = True
End With

“We found a problem with some content in ‘filename.xlsx’. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes. then i add in immediate window this and press enter and this "? application.StartupPath" appear expected : line number or label or statement or end of statement

Thank you its working great. royUK Yes it is so simple without VBA but if i use the formula there are approximately 500K rows which will definitely increase the file size using formula which makes it slow.

I have been striving to create a solution that is Increment the Number. Where from Column B data is start that is row 18 so code will copy value from the Column A row 17 to the last row of Column C with incrementing the number.

I'm hoping you can help me with.

Data

Result will be like this in Column A

Sub IncrmentNumber()

Dim ws          As Worksheet, LRow As Long, r As Long
Set ws = Sheet1
Application.ScreenUpdating = False
LastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

For r = 2 To LastRow

If ws.Cells(r, "C") <> "" Then

ws.Cells(r - 1, "A").Value = ws.Cells(r, "A").Value + 1

End If
Next

Application.ScreenUpdating = True
End Sub

Sure, Thanks.

Thank you very much. It works like charm.

royUK Hi, I am using this and its working fine but it locks the cell when user- form is open. Then i close the user-form and go to next cell. is it possible to move to next or any cell even if user-form is open.

Thank you very much royUK I have been struggling with Bubble sort since couple of hours. You are Master.

Yeah when i downloaded it was not adjusting it that's why asked. Can you please give me one more favor that is to sort this Listbox A to Z instead of sorting Sheet2 Column.

Yes royUK you did it thank you so much.

I am unable to do one more thing that is to adjust the Listbox size accordingly. why? any reason.

It appears like this:

No, the sorting should be normal like i selected the data from A2:A and sorted it through by right clicking the mouse and then press sort A to Z.

and i wanted to populate the ListBox on the Sheet1 Column A2:A upon selection of cell. for example i selected the Cell A15 from Sheet1 where Listbox is populated then i choose the name and the choosed name will be pasted into Cell A15

Ok no problem it would be fine and please add one more thing that is sorting A to Z. Thanks

Mumps Yes you guessed correctly but i want to done his through ListBox.

I hope to get some help. I am trying to create a Listbox that takes the data from Sheet2 Column A.

and populate the ListBox on sheet1 Range("A:A") on selection change.

The purpose to creating the listbox is to enter the names on Sheet1 Column A cells through Listbox.

Your help will be appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A:A")) Is Nothing Then


Dim ws      As Worksheet
Dim rng     As Range
Dim MyArray

Set ws = Sheets("Sheet2")


Set rng = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

With Me.ListBox1
    .Clear
    .ColumnHeads = False
    .ColumnCount = rng.Columns.Count


     MyArray = rng
    .List = MyArray

End With
End If
End Sub