回答編集履歴
1
テーブルとして書式設定するかを選択可能に変更
test
CHANGED
|
@@ -46,6 +46,10 @@
|
|
|
46
46
|
|
|
47
47
|
Const tableName = "データテーブル"
|
|
48
48
|
|
|
49
|
+
'テーブルとして書式設定するか
|
|
50
|
+
|
|
51
|
+
Const listFlag = False
|
|
52
|
+
|
|
49
53
|
|
|
50
54
|
|
|
51
55
|
'左端列格納配列
|
|
@@ -220,51 +224,75 @@
|
|
|
220
224
|
|
|
221
225
|
|
|
222
226
|
|
|
223
|
-
'
|
|
227
|
+
'リスト削除用
|
|
224
|
-
|
|
225
|
-
If Range(outputListStartPosition).Row > 1 Then
|
|
226
|
-
|
|
227
|
-
Range(Me.Range(outputListStartPosition).Offset(-1), Me.Range(outputListStartPosition).Offset(-1, pasteColumnShift + WEIGHTcolShift)) _
|
|
228
|
-
|
|
229
|
-
= Split("ID,TYPE,NUMBER,DATA,WEIGHT", ",")
|
|
230
|
-
|
|
231
|
-
End If
|
|
232
228
|
|
|
233
229
|
Dim delList As Variant
|
|
234
230
|
|
|
231
|
+
'リスト化
|
|
232
|
+
|
|
233
|
+
If listFlag Then
|
|
234
|
+
|
|
235
|
+
'（見出し,セル幅設定）（テーブル化）
|
|
236
|
+
|
|
237
|
+
If Range(outputListStartPosition).Row > 1 Then
|
|
238
|
+
|
|
239
|
+
Range(Me.Range(outputListStartPosition).Offset(-1), Me.Range(outputListStartPosition).Offset(-1, pasteColumnShift + WEIGHTcolShift)) _
|
|
240
|
+
|
|
241
|
+
= Split("ID,TYPE,NUMBER,DATA,WEIGHT", ",")
|
|
242
|
+
|
|
235
|
-
For Each delList In Me.ListObjects
|
|
243
|
+
For Each delList In Me.ListObjects
|
|
236
|
-
|
|
244
|
+
|
|
237
|
-
If delList.Name = tableName Then
|
|
245
|
+
If delList.Name = tableName Then
|
|
238
|
-
|
|
246
|
+
|
|
239
|
-
delList.Unlist
|
|
247
|
+
delList.Unlist
|
|
248
|
+
|
|
249
|
+
End If
|
|
250
|
+
|
|
251
|
+
Next
|
|
252
|
+
|
|
253
|
+
'テーブル化
|
|
254
|
+
|
|
255
|
+
Range(Me.Range(outputListStartPosition).Offset(-1), outputListLastPosition).ClearFormats
|
|
256
|
+
|
|
257
|
+
ListObjects.Add( _
|
|
258
|
+
|
|
259
|
+
xlSrcRange, _
|
|
260
|
+
|
|
261
|
+
Range(Me.Range(outputListStartPosition).Offset(-1), outputListLastPosition), , _
|
|
262
|
+
|
|
263
|
+
, xlYes, "TableStyleMedium1").Name = tableName
|
|
264
|
+
|
|
265
|
+
|
|
266
|
+
|
|
267
|
+
'ソート
|
|
268
|
+
|
|
269
|
+
Range(tableName).Sort , _
|
|
270
|
+
|
|
271
|
+
key1:=Me.Range(outputListStartPosition).Offset(, pasteColumnShift + WEIGHTcolShift), order1:=xlAscending, _
|
|
272
|
+
|
|
273
|
+
key2:=Me.Range(outputListStartPosition).Offset(, NUMcol), order2:=xlAscending, _
|
|
274
|
+
|
|
275
|
+
Header:=xlYes
|
|
240
276
|
|
|
241
277
|
End If
|
|
242
278
|
|
|
243
|
-
|
|
279
|
+
|
|
244
|
-
|
|
280
|
+
|
|
245
|
-
|
|
281
|
+
'リスト化しない
|
|
246
|
-
|
|
247
|
-
|
|
282
|
+
|
|
248
|
-
|
|
249
|
-
ListObjects.Add( _
|
|
250
|
-
|
|
251
|
-
|
|
283
|
+
Else
|
|
252
|
-
|
|
253
|
-
|
|
284
|
+
|
|
254
|
-
|
|
255
|
-
, xlYes, "TableStyleMedium1").Name = tableName
|
|
256
|
-
|
|
257
|
-
|
|
258
|
-
|
|
259
|
-
'ソート
|
|
285
|
+
'ソート
|
|
260
|
-
|
|
286
|
+
|
|
261
|
-
Range(
|
|
287
|
+
Range(Me.Range(outputListStartPosition), outputListLastPosition).Sort , _
|
|
262
|
-
|
|
288
|
+
|
|
263
|
-
key1:=Me.Range(outputListStartPosition).Offset(, pasteColumnShift + WEIGHTcolShift), order1:=xlAscending, _
|
|
289
|
+
key1:=Me.Range(outputListStartPosition).Offset(, pasteColumnShift + WEIGHTcolShift), order1:=xlAscending, _
|
|
264
|
-
|
|
290
|
+
|
|
265
|
-
key2:=Me.Range(outputListStartPosition).Offset(, NUMcol), order2:=xlAscending, _
|
|
291
|
+
key2:=Me.Range(outputListStartPosition).Offset(, NUMcol), order2:=xlAscending, _
|
|
266
|
-
|
|
292
|
+
|
|
267
|
-
Header:=xl
|
|
293
|
+
Header:=xlNo
|
|
294
|
+
|
|
295
|
+
End If
|
|
268
296
|
|
|
269
297
|
|
|
270
298
|
|
|
@@ -278,4 +306,12 @@
|
|
|
278
306
|
|
|
279
307
|
実行後イメージは下記のとおりです。
|
|
280
308
|
|
|
309
|
+
・listFlag = True
|
|
310
|
+
|
|
281
311
|
|
|
312
|
+
|
|
313
|
+
・listFlag = False
|
|
314
|
+
|
|
315
|
+
|
|
316
|
+
|
|
317
|
+
|