Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 49
Default Copy rows as many there are 'slashes' in the cell and split out thecontent of that cell in the copied rows

Hello,

An action which I do mostly by hand, row for row, perhaps is possible to do automaticly.

In a sheet column A till Z, is in column F in some cases text registered with a 'slash' in between if more then one text occur in the specific cell.

If Cell F contains more textfields, and then there also slashes, then I split it out in identical copied rows, with only in column F the text as standalone separated part.

The basic row stays as it is, only if there are for example 3 slashes in column F then I insert 3 rows below this basic row, copy the complete row, and fill in cell F only that part of the text.
If there are no slashes then no copy action required.

For example.

Row.....ColumnF
2.......aa/bb/cc
3.......dd/ee
4.......ff
5.......gg/hh/ii
6.......jj

results then in

Row1....ColumnF
2.......aa/bb/cc
3.......aa
4.......bb
5.......cc
6.......dd/ee
7.......dd
8.......ee
9.......ff
10......gg/hh/ii
11......gg
12......hh
13......ii
14......jj

If somebody like to solve :)
Then I can stop doing it by hand.

regards, Johan

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy rows as many there are 'slashes' in the cell and split out the content of that cell in the copied rows

Hi Johan,

Am Sun, 22 Sep 2019 01:50:57 -0700 (PDT) schrieb JS SL:

The basic row stays as it is, only if there are for example 3 slashes in column F then I insert 3 rows below this basic row, copy the complete row, and fill in cell F only that part of the text.
If there are no slashes then no copy action required.

For example.

Row.....ColumnF
2.......aa/bb/cc
3.......dd/ee
4.......ff
5.......gg/hh/ii
6.......jj

results then in

Row1....ColumnF
2.......aa/bb/cc
3.......aa
4.......bb
5.......cc
6.......dd/ee
7.......dd
8.......ee
9.......ff
10......gg/hh/ii
11......gg
12......hh
13......ii
14......jj


try:

Sub CopyRows()
Dim rngC As Range
Dim varTmp As Variant
Dim LRow As Long, i As Long

Application.ScreenUpdating = False
With ActiveSheet
LRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = LRow To 2 Step -1
If InStr(.Cells(i, "F"), "/") 0 Then
varTmp = Split(.Cells(i, "F"), "/")
.Rows(i).Copy
.Rows(i + 1).Resize(UBound(varTmp) + 1).Insert shift:=xlDown
.Cells(i + 1, "F").Resize(UBound(varTmp) + 1) = _
Application.Transpose(varTmp)
End If
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 49
Default Copy rows as many there are 'slashes' in the cell and split outthe content of that cell in the copied rows

Why looks life sometimes so nice :)
Claus, thx (again). It really help me out.
regards, Johan
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 49
Default Copy rows as many there are 'slashes' in the cell and split outthe content of that cell in the copied rows

Just a small question......

How to mark the cell column F with the slashes 'green' (that are the one to be copied) and then the one that you splitted out and copied, the cell in column F 'yellow'.
Then it's easy to see which one was the basic (= green) and the copied/splitted ones (=yellow).

reg. Johan
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy rows as many there are 'slashes' in the cell and split out the content of that cell in the copied rows

Hi Johan,

Am Sun, 22 Sep 2019 22:56:49 -0700 (PDT) schrieb JS SL:

How to mark the cell column F with the slashes 'green' (that are the one to be copied) and then the one that you splitted out and copied, the cell in column F 'yellow'.
Then it's easy to see which one was the basic (= green) and the copied/splitted ones (=yellow).


change the code to:

Sub CopyRows()
Dim rngC As Range
Dim varTmp As Variant
Dim LRow As Long, i As Long

Application.ScreenUpdating = False
With ActiveSheet
LRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = LRow To 2 Step -1
If InStr(.Cells(i, "F"), "/") 0 Then
varTmp = Split(.Cells(i, "F"), "/")
.Cells(i, "F").Interior.Color = vbGreen
.Rows(i).Copy
.Rows(i + 1).Resize(UBound(varTmp) + 1).Insert shift:=xlDown
With .Cells(i + 1, "F").Resize(UBound(varTmp) + 1)
.Value = Application.Transpose(varTmp)
.Interior.Color = vbYellow
End With
End If
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Windows10
Office 2016


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 49
Default Copy rows as many there are 'slashes' in the cell and split outthe content of that cell in the copied rows

Great !! Thx :)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to split one cell into multiple rows? wondering Excel Discussion (Misc queries) 2 April 3rd 23 10:55 AM
HELP! Split cell contents to new rows nyarce Excel Discussion (Misc queries) 4 May 13th 10 06:45 PM
Need to split a cell into multiple rows [email protected] Excel Programming 2 April 1st 08 06:59 PM
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows Corey Excel Programming 2 August 1st 07 02:02 AM
Split Cell Into Seperate Rows Andibevan[_4_] Excel Programming 2 December 20th 05 07:35 PM


All times are GMT +1. The time now is 02:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"