Loop through a range of cells and skip blank cells.

testrad

New Member
Joined
May 20, 2015
Messages
6
I have a spreadsheet that survey's equipment condition for equipment condition for multiple places, which is designated by ID (see Data Input example). For each row I need the equipment under the columns header "E1" and "E1C" copy/pasted into another worksheet ("Data Output") with the ID number associtated with that row and then "E2" & "E2C" copy pasted into next blank row with the same ID. Keep looping through the columns and skip the nonblank cells in each row. After going through all the columns in a row it should go to the next row until there is a row with no data (blank row).
Data Input example (smaller example of what the table looks like)

IDE1E1CE2E2CE3E3CE4E4CE5E5CET5C
10
11N600D9N600D9N600D9N2000D+9
12
13N2000D+2N2000D 5
14
15
16
17N2000D+9N2000D+0N2000D+7
18
19
20
………………………………
195N600D9N2000D+8

<colgroup><col><col><col span="10"></colgroup><tbody>
</tbody>

The "Data Output Example" image can help explain what I mean.

IDModelCondition
11N600D9
11N600D9
11N600D9
11N600D9
13N2000D+2
13N2000D5
17N2000D+9
17N2000D+0
17N2000D+7
………
195N600D9
195N2000D+8


<colgroup><col><col><col></colgroup><tbody>
</tbody>

The code i have so far does what the intended output should do, but it is hard coded instead of looping through the rows/columns and automatically skipping blank cells.

Option Explicit
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False


Dim source As Worksheet, target As Worksheet
Dim r As Range, rB As Range

'range is B:L. B8:L8 empty so skipped
'next is B9:L9. skip J9:L9 becuase empty
Sheets("Source").Range("B9:C9,A9").Copy
Sheets("ET target").Range("A2").PasteSpecial xlValues
Sheets("Source").Range("D9:E9,A9").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("D9:E9,A9").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("F9:G9,A9").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("H9:I9,A9").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
'Skip B10:L10 empty. Next is B11:L11. Skip F11:L11 becuase empty
Sheets("Source").Range("B11:C11,A11").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("D11:E11,A11").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
'Skip B12:L14 becuase empty. Next is B15:L15. skip H15:L15 becuase empty
Sheets("Source").Range("B15:C15,A15").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("D15:E15,A15").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("F15:G15,A15").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
'Repeat for upto 200 rows.



Application.ScreenUpdating = True


End Sub

Sorry if there is duplicate threads somewhere. I have looked through quite a few posts and did not find anything that i could tailor to fit. Thanks for your help!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try this

Code:
Sub Loop_range()
  Dim sh1 As Worksheet, sh2 As Worksheet, ar As Range, j As Long
  Set sh1 = Sheets("Source")
  Set sh2 = Sheets("ET target")
  For Each ar In sh1.Range("B1", sh1.Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    For j = 2 To sh1.Cells(ar.Row, Columns.Count).End(xlToLeft).Column Step 2
      sh2.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, 3).Value = Array(ar.Offset(, -1), sh1.Cells(ar.Row, j), sh1.Cells(ar.Row, j + 1))
    Next
  Next
End Sub
 
Upvote 0
Here is another approach. In particular, this would be much faster if the data is large.
I have assumed that data starts in cell A1 of "Source" and that "ET Target" is empty when the code is run. If either assumption is incorrect, more details please.
Test in a copy of your workbook.

Code:
Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long, maxnum As Long
  
  With Sheets("Source")
    a = .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Resize(, .Cells(1, .Columns.Count).End(xlToLeft).Column).Value
    uba2 = UBound(a, 2)
    maxnum = .Range("B2").Resize(UBound(a) - 1, uba2 - 1).SpecialCells(xlConstants).Count / 2
  End With
  ReDim b(1 To maxnum, 1 To 3)
  For i = 2 To UBound(a)
    For j = 2 To uba2 Step 2
      If Len(a(i, j)) > 0 Then
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = a(i, j): b(k, 3) = a(i, j + 1)
      End If
    Next j
  Next i
  With Sheets("ET Target")
    .Range("A2").Resize(k, 3).Value = b
    .Range("A1:C1").Value = Array("ID", "Model", "Condition")
    .UsedRange.Columns.AutoFit
  End With
End Sub
 
Upvote 0
Sorry for the slow reply i wasn't able to download the copy and screen shot files to my work computer, so i had to get the files transferred to a personal computer. (thanks btw for the URLs)

I realize that I over-simplified my "Data Input Example", now that i see how your codes work. My example Input/Output showed what should happen for one region of the spreadsheet; i.e. 1 type of equipment on the survey. There is actually multiple regions that need to get pasted into separate sheets. Additionally there is columns with user info that should not get copied into the other sheets. My initial thought was that if i could get the code to work for 1 region i could duplicate to work for the other regions.

the table below is is has all of the columns in the spreadsheet but not all the rows. additionally the user info is hidden (columns B:AK)
Revised data input example:

Excel 2016 (Windows) 64 bit
A
AQ
AR
AS
AT
AU
AV
AW
AX
AY
AZ
BA
BB
BC
BD
BE
BF
BG
BH
BI
BJ
BK
BL
BM
BN
BO
BP
BQ
BR
BS
BT
BU
BV
BW
BX
BY
BZ
CA
CB
CC
CD
CE
CF
CG
CH
CI
CJ
CK
CL
CM
CN
CO
CP
CQ
CR
CS
CT
CU
CV
CW
CX
CY
CZ
DA
DB
DC
DD
DE
DF
DG
DH
DI
DJ
DK
DL
DM
DN
DO
DP
DQ
DR
DS
DT
DU
DV
1
ID
ET1
ET1C
ET2
ET2C
ET3
ET3C
ET4
ET4C
ET5
ET5C
ET6
ET6C
ET7
ET7C
ET8
ET8C
ET1-MOBILITY
ET1C-MOBILITY
ET2-MOBILITY
ET2C-MOBILITY
UT1
UT1C
UT2
UT2C
UT3
UT3C
UT4
UT4C
UT5
UT5C
UT6
UT6C
UT1-MOBILITY
UT1C-MOBILITY
UT2-MOBILITY
UT2C-MOBILITY
BT1
BT1C
BT2
BT2C
BT3
BT3C
BT4
BT4C
BT1-MOBILITY
BT1C-MOBILITY
BT2-MOBILITY
BT2C-MOBILITY
RT1
RT1C
RT2
RT2C
RT3
RT3C
RT1-MOBILITY
RT1C-MOBILITY
RT2-MOBILITY
RT2C-MOBILITY
INT1
INT1C
INT2
INT2C
INT3
INT3C
INT1-MOBILITY
INT1C-MOBILITY
INT2-MOBILITY
INT2C-MOBILITY
CR1
CR1C
CR2
CR2C
CR3
CR3C
CR1-MOBILITY
CR1C-MOBILITY
CR2-MOBILITY
CR2C-MOBILITY
Survey Meter
Survey MeterC
MPI1
MPI1C
FPI1
FPI1C
2
10​
3
11​
N600D
9​
N600D
9​
N600D
9​
N2000D+
9​
Sonic 1200M
9​
GE USN-52
8​
GE USN-52
8​
GE USN-52
0​
BM1000
9​
BM1000
9​
BM600
9​
LPX-160
9​
LPX-160
8​
LPX-160
8​
TSG 501015 (newer)
6​
TSG 501013 (older)
0​
QTY 2+ 451P, QTY 2+ SM-400
8​
GB3509A-01
9​
GB MA-2
9​
4
12​
5
13​
N2000D+
9​
N2000D
9​
GE USN-60
9​
GE USN-60
9​
GB3509A-01
9​
Other
9​
6
14​
7
15​
8
16​
9
17​
N2000D+
9​
N2000D+
9​
N2000D+
9​
N2000D+
9​
GE USN-60
9​
GE USN-60
9​
LPX-160
9​
TSG 501013 (older)
6​
QTY 2+ 451P, QTY 4+ SM-400
7​
GB3509A-01
1​
GB PT-48
9​
10
18​
11
19​
12
20​
N600D
9​
N600D
9​
N600D
9​
N600D
9​
N600DN600D
9​
N600D
9​
Sonic 1200M
9​
GE USN-60
9​
GE USN-60
0​
Bondmaster
9​
Bondmaster
9​
LPX-160
9​
LPX-160
9​
LPX-160
0​
TSG 501013 (older)
9​
TSG 501013 (older)
9​
TSG 501013 (older)
9​
GE CRx Flex II
6​
GE CRx Flex II
6​
GE CR-50P
7​
QTY 6+ 451P, no SM-400
8​
Other
9​
GB PT-36
9​
Sheet: Source
 
Upvote 0
Sorry for the slow reply i wasn't able to download the copy and screen shot files to my work computer, so i had to get the files transferred to a personal computer. (thanks btw for the URLs)

I realize that I over-simplified my "Data Input Example", now that i see how your codes work. My example Input/Output showed what should happen for one region of the spreadsheet; i.e. 1 type of equipment on the survey. There is actually multiple regions that need to get pasted into separate sheets. Additionally there is columns with user info that should not get copied into the other sheets. My initial thought was that if i could get the code to work for 1 region i could duplicate to work for the other regions.

the table below is is has all of the columns in the spreadsheet but not all the rows. additionally the user info is hidden (columns B:AK)

A couple of questions:
- Each region on a new sheet?
- Which columns should be copied?
 
Upvote 0
Yes, so there is 9 different types of equipment as you scroll through the columns(ET, UT, BT, RT, INT, CR, SM, MPI, & FPI). each one gets copied into a separate sheet.

The table below shows the different equipment types and the ranges of the columns. For example, ET equipment is range AQ2:BJ (upto XX number of rows). The ET data in each row would get copied into a sheet called "ET target". The next is UT equipment, whose range is BK2:BZ and would get copied into a sheet called "UT target". The output for all equipment is the same, just in different sheets (I.E. the output should be ID, model, condition).

Excel 2016 (Windows) 64 bit
U
18
55​
Sheet: Surveys Returned
 
Upvote 0
sorry about that. still figuring out the coping table tools :).

Excel 2016 (Windows) 64 bit
A
B
C
D
E
F
G
H
I
1
ET Equipment​
UT Equipment​
BT Equipment​
RT Equipment​
INT Equipment​
CR Equipment​
SM Equipment​
MPI Equipment​
FPI Equipment​
2
region 1​
region 2​
region 3​
region 4​
region 5​
region 6​
region 7​
region 8​
region 9​
3
AQ2:BJ200​
BK2:BZ200​
CA2:CL200​
CM2:CV200​
CW2:DF200​
DG2:DP200​
DQ2:D200​
DS2:DT200​
DU2:DV200​
Sheet: Sheet8
 
Upvote 0
I'm confused.
What is the real name of the regions. That is, in the headers from AQ1 to DV1.
Do you have:
ET1-MOBILITY
or
ET Equipment
or
Region 1


It is possible that you can put a list of names and the region on a sheet, something like this, each region in column B means a new sheet.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:136.87px;" /><col style="width:98.85px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >NOMBRE</td><td >REGION</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >ET1</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >ET1C</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >ET2</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >ET2C</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >ET3</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >ET3C</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >…</td><td >…</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >ET1-MOBILITY</td><td >REGION 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >ET1C-MOBILITY</td><td >REGION 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >ET2-MOBILITY</td><td >REGION 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >ET2C-MOBILITY</td><td >REGION 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >…</td><td >…</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >UT1</td><td >REGION 3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >UT1C</td><td >REGION 3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >UT2</td><td >REGION 3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >UT2C</td><td >REGION 3</td></tr></table>
 
Last edited:
Upvote 0
I guess I don't see what the importance of the headers is. The specific ranges of the data is described in my last table and the actual headers were in the "Revised Data Input example". I put "region" in the table because that is best how to describe how theres different sections. additionally i added "ET equipment" to give an idea of what equipment is in those regions. Ive added a table as requested. I hope that clears it up, If not i can try again. thanks for your help!

Excel 2016 (Windows) 64 bit
A
B
C
7
headers
regions
Actual Range
8
ET1
Region 1​
AQ2:BJ200 (or last row)​
9
ET1C
10
ET2
11
ET2C
12
13
ET8
14
ET8C
15
ET1-Mobility
16
ET1C-Mobility
17
ET1-Mobility
18
ET1C-Mobility
19
UT1
Region 2​
BK2:BZ200​
20
UT1C
21
UT2
22
UT2C
23
24
UT6
25
UT6C
26
UT1-Mobility
27
UT1C-Mobility
28
UT2-Mobility
29
UT2C-Mobility
30
BT1
Region 3​
CA2:CL200​
31
BT1C
32
33
BT1-Mobility
34
BT1C-Mobility
35
BT2-Mobility
36
BT2C-Mobility
37
RT1
Region 4​
CM2:CV200​
38
RT1C
39
40
RT1-Mobility
41
RT1C-Mobility
42
RT2-Mobility
43
RT2C-Mobility
44
INT1
Region 5​
CW2:DF200​
45
INTC
46
47
INT1-Mobility
48
INT1C-Mobility
49
INT2-Mobility
50
INT2C-Mobility
51
CR1
Region 6​
DG2:DP200​
52
CR1C
53
54
CR1-Mobility
55
CR1C-Mobility
56
CR2-Mobility
57
CR2C-Mobility
58
SM1
Region 7​
DQ2:DR200​
59
SM1C
60
61
SM1-Mobility
62
SM1C-Mobility
63
SM2-Mobility
64
SM2C-Mobility
65
MPI1C
Region 8​
DS2:DT200​
66
MPI1C
67
FPI1
Region 9​
DU2:DV200​
68
FPI1C
Sheet: Sheet8
 
Upvote 0
The idea is to find a pattern in the header to determine when to create each sheet. You didn't explain the range of columns, maybe it's clear to you, but I don't know your information. Can the ranges be on the sheet8 or maybe the first 2 letters of each region?
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top