代码替换表头,不会合并行 [英] Code replaces table headers and will not merge rows
本文介绍了代码替换表头,不会合并行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Sub SystemSize()
Dim LastRow As Long
LastRow = Range(I& Rows.Count).End(xlUp).Row
Dim I As Long,Groups As Long
Range(A2:I& LastRow)排序key1:=范围(I2),order1:= xlAscending'排序数据
组= 1
尽管组& 8
I = 2
选择案例组
案例1
对于j = 2 To LastRow
如果单元格(j,9)> = 6并且单元格(j,9)< = 8然后
单元格(j,1)=6-8 MTPH'单元格(j,1)
I = I + 1
End If
Next
案例2
对于j = 2 To LastRow
如果单元格(j,9)> ; = 10和细胞(j,9)<= 15然后
细胞(j,1)=10-15 MTPH
I = I + 1
End If
Next
案例3
对于j = 2 To LastRow
如果Cells(j,9)> = 16 And Cells(j, 9)< = 21然后
单元格(j,1)=16-21 MTPH
I = I + 1
结束如果
下一个
案例4
对于j = 2 To LastRow
如果单元格(j,9)> = 24和单元格(j,9)< = 28然后
单元格(j,1)=24-28 MTPH
I = I + 1
结束如果
下一个
案例5
对于j = 2 To LastRow
如果单元格(j,9)> = 30,而单元格(j,9)= 38则
单元格(j,1)=30-38 MTPH
如果
下一个
案例6
对于j = 2 To LastRow
如果单元格(j,9)> = 40和细胞(j,9)< = 48然后
细胞(j,1)=40-48 MTPH
I = I + 1
结束If
下一个
案例7'这被添加到拾取不属于组的数据,如8或9
对于j = 2 To LastRow
如果单元格(j,9)> ; 0和细胞(j,9) 6或细胞(j,9)> 48然后
单元格(j,1)=无组
I = I + 1
结束如果
下一个
结束选择
组=组+ 1
循环
结束子
解决方案
sort参数应该有一个选项来指定 Header = xlYes
或类似的
Range(A2:I& LastRow).Sort key1:= Range(I2),order1:= xlAscending,Header:= xlYes'
I am sorting this table by metric tons per hour, and the code I have so far deletes the headers(the second row down is just shifted up) and I can't figure out how to make this not happen. Also, I want to merge the cells in the far left column once the list is sorted so different number ranges are grouped off rather than stating the range in each row. I need the ranges to be 6-8, 10-15, 16-21, 24-28. Thanks in advance.
Sub SystemSize()
Dim LastRow As Long
LastRow = Range("I" & Rows.Count).End(xlUp).Row
Dim I As Long, Groups As Long
Range("A2:I" & LastRow).Sort key1:=Range("I2"), order1:=xlAscending 'Sorts data
Groups = 1
Do While Groups < 8
I = 2
Select Case Groups
Case 1
For j = 2 To LastRow
If Cells(j, 9) >= 6 And Cells(j, 9) <= 8 Then
Cells(j, 1) = "6-8 MTPH" 'Cells(j, 1)
I = I + 1
End If
Next
Case 2
For j = 2 To LastRow
If Cells(j, 9) >= 10 And Cells(j, 9) <= 15 Then
Cells(j, 1) = "10-15 MTPH"
I = I + 1
End If
Next
Case 3
For j = 2 To LastRow
If Cells(j, 9) >= 16 And Cells(j, 9) <= 21 Then
Cells(j, 1) = "16-21 MTPH"
I = I + 1
End If
Next
Case 4
For j = 2 To LastRow
If Cells(j, 9) >= 24 And Cells(j, 9) <= 28 Then
Cells(j, 1) = "24-28 MTPH"
I = I + 1
End If
Next
Case 5
For j = 2 To LastRow
If Cells(j, 9) >= 30 And Cells(j, 9) <= 38 Then
Cells(j, 1) = "30-38 MTPH"
End If
Next
Case 6
For j = 2 To LastRow
If Cells(j, 9) >= 40 And Cells(j, 9) <= 48 Then
Cells(j, 1) = "40-48 MTPH"
I = I + 1
End If
Next
Case 7 'this added to pick up data that does not fall into a group, like 8 or 9
For j = 2 To LastRow
If Cells(j, 9) > 0 And Cells(j, 9) < 6 Or Cells(j, 9) > 48 Then
Cells(j, 1) = "No Group"
I = I + 1
End If
Next
End Select
Groups = Groups + 1
Loop
End Sub
解决方案
the sort parameter should have an option to specify Header=xlYes
or similar
Range("A2:I" & LastRow).Sort key1:=Range("I2"), order1:=xlAscending, Header:= xlYes 'Sorts data
这篇关于代码替换表头,不会合并行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文