如何使用特定数据替换列内容 [英] How to replace the columns content with specific data
本文介绍了如何使用特定数据替换列内容的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
id name sal
0 abc 100
123 bcd 200
ss cdf 300
0 dfg 400
id name sal
0 ghi 500
234 hij 600
sd ojk 400
输出像这样
id name sal
123 abc 100
123 bcd 200
123 cdf 300
123 dfg 400
234 ghi 500
234 hij 600
234 ojk 400
我的尝试: < br $>
Sub DeleteRowsWithX()
maxRow = ActiveSheet.UsedRange.Rows.Count
对于 i = 2 maxRow
(StrComp (ActiveSheet.Cells(i, 2 )。文本, id,vbTextCompare)= 0 )
行(i)。选择
Selection.Delete Shift:= xlUp
循环
下一步
结束 Sub
解决方案
试试这个:
< pre lang =VB.NET> 选项 明确
Sub Cleaning()
Dim wsh As 工作表
Dim i 作为 整数
' context
设置 wsh = ThisWorkbook.Worksheets( 1 )
' 起始行
i = 2
' 直到col中的单元格。 A不为空
wsh.Range( A& i)<>
' < span class =code-comment> if zero - 从单元格下方获取值
如果 wsh.Range( A& i)= 0 然后
wsh.Range( A& ; i)= wsh.Range( A& i).Offset(RowOffset:= 1 )
结束 如果
' 如果单元格包含非数字值而且它不是id - 从单元格上方获取值
如果 LCase(wsh.Range( A & i))<> id 和 不 IsNumeric(wsh.Range( A& i))然后
wsh.Range( A& i)= wsh.Range( A& i ).Offset(RowOffset:= - 1)
结束 如果
' ifid - 删除整行
如果 LCase(wsh.Range( A& i))= id 然后
wsh.Range ( A& i).EntireRow.Delete xlShiftUp
i = i - 1
结束 如果
i = i + 1
Loop
结束 Sub
结果:
123 abc 100
123 bcd 200
123 cdf 300
234 ghi 500
234 hij 600
234 ojk 400
vanga srikar reddy [ ^ ]写道:
您好maciej
感谢您的解决方案
但要求已更改
如下所示
输入:
id name sal
0 ABC 500
ABC001 BCD 400
0 DEF 400
xyz.ab EFG 200
0 FGH 300
BCD GHI 400
id name sal
0 HIJ 300
ABC002 IJK 400
0 JKL 500
xyz.ab KLM 600
0 LMN 700
CDE MNO 800
所需输出如下
id name sal
ABC001 ABC 500
ABC001 BCD 400
ABC001 DEF 400
ABC001 EFG 200
ABC001 FGH 300
ABC001 GHI 400
ABC002 HIJ 300
ABC002 IJK 400
ABC002 JKL 500
ABC002 KLM 600
ABC002 LMN 700
ABC002 MNO 800
提前致谢
Sub Cleaning2()
Dim wsh 作为工作表
Dim i 作为 整数 ' ,idcounter As Integer
' context
设置 wsh = ThisWorkbook.Worksheets( 1 )
'
' idcounter = 1
' 起始行
i = 2
' 直到col的单元格。 A不为空
wsh.Range( A& i)<>
' < span class =code-comment> if zero - 从单元格下方获取值
如果 wsh.Range( A& i)= 0 然后
wsh.Range( A& ; i)= wsh.Range( A& i).Offset(RowOffset:= 1 )
结束 如果
' 如果单元格包含非数字值而且它不是id - 从单元格上方获取值
如果 LCase(wsh.Range( A & i))<> id 和 不 IsNumeric(wsh.Range( A& i))和 不 wsh.Range( A& i)赞 wsh.Range( B2)& * 然后
wsh.Range( A& i)= wsh.Range( A& i).Offset(RowOffset:= - 1)
结束 如果
' ifid - 删除整行
如果 LCase(wsh.Range( A& i))= id 然后
wsh.Range( A& i).EntireRow.Delete xlShiftUp
i = i - 1
结束 如果
i = i + 1
循环
结束 子跨度>
id name sal
0 abc 100
123 bcd 200
ss cdf 300
0 dfg 400
id name sal
0 ghi 500
234 hij 600
sd ojk 400
output Like this
id name sal
123 abc 100
123 bcd 200
123 cdf 300
123 dfg 400
234 ghi 500
234 hij 600
234 ojk 400
What I have tried:
Sub DeleteRowsWithX()
maxRow = ActiveSheet.UsedRange.Rows.Count
For i = 2 To maxRow
Do While (StrComp(ActiveSheet.Cells(i, 2).Text, "id", vbTextCompare) = 0)
Rows(i).Select
Selection.Delete Shift:=xlUp
Loop
Next
End Sub
解决方案
Try this:
Option Explicit Sub Cleaning() Dim wsh As Worksheet Dim i As Integer 'context Set wsh = ThisWorkbook.Worksheets(1) 'starting row i = 2 'till cell in col. A is not empty Do While wsh.Range("A" & i) <> "" 'if zero - get value from below cell If wsh.Range("A" & i) = 0 Then wsh.Range("A" & i) = wsh.Range("A" & i).Offset(RowOffset:=1) End If 'if cell contains non-numeric value and it's not a "id" - get value from above cell If LCase(wsh.Range("A" & i)) <> "id" And Not IsNumeric(wsh.Range("A" & i)) Then wsh.Range("A" & i) = wsh.Range("A" & i).Offset(RowOffset:=-1) End If 'if "id" - remove entire row If LCase(wsh.Range("A" & i)) = "id" Then wsh.Range("A" & i).EntireRow.Delete xlShiftUp i = i - 1 End If i = i + 1 Loop End Sub
Result:
123 abc 100 123 bcd 200 123 cdf 300 234 ghi 500 234 hij 600 234 ojk 400
vanga srikar reddy[^] wrote:
Hi maciej
Thanks for your solution
but requirement has changed
as below
Input :
id name sal 0 ABC 500 ABC001 BCD 400 0 DEF 400 xyz.ab EFG 200 0 FGH 300 BCD GHI 400 id name sal 0 HIJ 300 ABC002 IJK 400 0 JKL 500 xyz.ab KLM 600 0 LMN 700 CDE MNO 800
required output as below
id name sal ABC001 ABC 500 ABC001 BCD 400 ABC001 DEF 400 ABC001 EFG 200 ABC001 FGH 300 ABC001 GHI 400 ABC002 HIJ 300 ABC002 IJK 400 ABC002 JKL 500 ABC002 KLM 600 ABC002 LMN 700 ABC002 MNO 800
Thanks in advance
Sub Cleaning2() Dim wsh As Worksheet Dim i As Integer ', idcounter As Integer 'context Set wsh = ThisWorkbook.Worksheets(1) ' 'idcounter = 1 'starting row i = 2 'till cell in col. A is not empty Do While wsh.Range("A" & i) <> "" 'if zero - get value from below cell If wsh.Range("A" & i) = 0 Then wsh.Range("A" & i) = wsh.Range("A" & i).Offset(RowOffset:=1) End If 'if cell contains non-numeric value and it's not a "id" - get value from above cell If LCase(wsh.Range("A" & i)) <> "id" And Not IsNumeric(wsh.Range("A" & i)) And Not wsh.Range("A" & i) Like wsh.Range("B2") & "*" Then wsh.Range("A" & i) = wsh.Range("A" & i).Offset(RowOffset:=-1) End If 'if "id" - remove entire row If LCase(wsh.Range("A" & i)) = "id" Then wsh.Range("A" & i).EntireRow.Delete xlShiftUp i = i - 1 End If i = i + 1 Loop End Sub
这篇关于如何使用特定数据替换列内容的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文