C
Cloudfall
Hi everyone,
I have duplicate data and I need to process it accordingly. I followed
some advice I found in this group and went to:
http://www.cpearson.com/excel/topic.htm
There I found the following code (reproduced here exactly) which looked
like it could be modified to do the job I require:
Sub FixDuplicateRows()
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value
Then
Cells(RowNdx, ColNum).Value = "----"
End If
Next RowNdx
End Sub
I created the following data in column 1:
Heading 1
a
b
b
c
I physically selected column 1 and ran the code with the locals window
open. The first number RowNdx was assigned was 65,536. The code above
works if you just select cells A1 to A5 where RowNdx is assigned the
value 5.
So I added the following lines of code just before "ColNum =
Selection(1).Column":
Range("A1").Select
Selection.End(xlDown).Select
This didn't work and I don't know why not. Incidently, I don't really
understand what the following mean:
(1) "Selection(1).Column" (I assume the 1 means you can have more than
1 selection and that this returns the column number of the thing
selected, but if that is the case, what on earth does
"Selection(Selection.Cells.Count).Row" mean?)
(2) "Selection(1).Row" (I assume this somehow returns the number of the
row)
Going to my main reference, Que's "Using Excel Visual Basic", left my
no wiser. I don't blame the book. I blame the complexity of VBA for
this. I have never properly understood how the Object Browser could be
of any earthly use to me. I need something like the "Complete Idiot's
Guide to Excel VBA for Total Dummies Who Aren't Very Smart At All".
It's the basics I lack. Is there something somewhere that simply
explains them? I know it can be done. I read a great article called
"Access, VBA, and Visual Basic Debugging Tips and Techniques" by Luke
Chung (President of FMS) which for the first time explained in simple
terms how to use the IDE. I have this article and refer to it
constantly. Are there simple explanations for how VBA works out there
anywhere? I can't tell you how frustrated I am. I don't want to keep
asking you people how to do stuff and waste your time. You have lives
too. I'm very sorry about this rant but I had to get this off my chest.
Does anybody know how the above code could be modified to select just
the cells with data in them? Thank you.
Regards,
Terry.
I have duplicate data and I need to process it accordingly. I followed
some advice I found in this group and went to:
http://www.cpearson.com/excel/topic.htm
There I found the following code (reproduced here exactly) which looked
like it could be modified to do the job I require:
Sub FixDuplicateRows()
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value
Then
Cells(RowNdx, ColNum).Value = "----"
End If
Next RowNdx
End Sub
I created the following data in column 1:
Heading 1
a
b
b
c
I physically selected column 1 and ran the code with the locals window
open. The first number RowNdx was assigned was 65,536. The code above
works if you just select cells A1 to A5 where RowNdx is assigned the
value 5.
So I added the following lines of code just before "ColNum =
Selection(1).Column":
Range("A1").Select
Selection.End(xlDown).Select
This didn't work and I don't know why not. Incidently, I don't really
understand what the following mean:
(1) "Selection(1).Column" (I assume the 1 means you can have more than
1 selection and that this returns the column number of the thing
selected, but if that is the case, what on earth does
"Selection(Selection.Cells.Count).Row" mean?)
(2) "Selection(1).Row" (I assume this somehow returns the number of the
row)
Going to my main reference, Que's "Using Excel Visual Basic", left my
no wiser. I don't blame the book. I blame the complexity of VBA for
this. I have never properly understood how the Object Browser could be
of any earthly use to me. I need something like the "Complete Idiot's
Guide to Excel VBA for Total Dummies Who Aren't Very Smart At All".
It's the basics I lack. Is there something somewhere that simply
explains them? I know it can be done. I read a great article called
"Access, VBA, and Visual Basic Debugging Tips and Techniques" by Luke
Chung (President of FMS) which for the first time explained in simple
terms how to use the IDE. I have this article and refer to it
constantly. Are there simple explanations for how VBA works out there
anywhere? I can't tell you how frustrated I am. I don't want to keep
asking you people how to do stuff and waste your time. You have lives
too. I'm very sorry about this rant but I had to get this off my chest.
Does anybody know how the above code could be modified to select just
the cells with data in them? Thank you.
Regards,
Terry.