Importing selected cells into VBA

**VBA Noob** · 10-12-2008, 05:23 PM

See if this helps

Dim c As Range
Dim NewSrt As String
    For Each c In Selection
        NewSrt = NewSrt & "," & c.Value
    Next
Selection(1).Value = Mid(NewSrt, 2, 9 ^ 9)

VBA Noob

**tryer** · 10-12-2008, 05:45 PM

Thanks Noob that works a treat.

Is there a way to delete the cells below selection(1) ? at the same time?

**VBA Noob** · 10-12-2008, 05:46 PM

Add

c.Clear

after

NewSrt = NewSrt & "," & c.Value

VBA Noob

**tryer** · 10-12-2008, 05:48 PM

Noob this works perfectly. However I'm struggling to understand the line

Selection(1).Value = Mid(NewSrt, 2, 9 ^ 9)

presumably Selection (1) selects the first cell in the range but don't know what the 9^9 bit does?

**VBA Noob** · 10-12-2008, 05:52 PM

I use the mid function to get rid of the extra comma at the start.
Type =9^9 in a cell and it returns 387420489. This makes sure it shouldn't cut off any cells at the end of the mid function

Selection (1) selects the first cell in the range

Correct

VBA Noob

**tryer** · 10-12-2008, 05:54 PM

Thanks that's great

Importing selected cells into VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

trying to understand

Thread Information

Users Browsing this Thread

Similar Threads

average of cells from user selected range of worksheets

bug: cells selected as mouse moves

Manual calculating selected cells only

importing text but it spreads across cells

Bookmarks

Bookmarks

Posting Permissions