Hi All,
I have tried hard to figure this out, but to no avail. All I want to
do is select multiple rows (or multiple anything for that matter) in
VBA, mimicking when you use CTRL-Click in Excel. I have tried two
approchaes:
Approach 1: Use some sort of "add to selection" method in VBA. Does
such a thing exist? The Macro Recorder just includes all the selected
rows into a single range.
Approach 2: Toggle the "Add to Selection" Mode, make the selections,
then toggle again. SHIFT-F8 does this in Excel, so I tried to use
SendKeys to do it:
SendKeys "+{F8}"
Sendkeys doesn't quite work like I thought it did, first doing nothing
when I step through the code, then when I run the code, it puts the
keystrokes into the code iteself! (Example: SendKeys "Hello" puts
"Hello" in the code wherever the cursor was)
While I'm at it, I'll explain the context as well. I need to delete all
rows except the Nth rows of a worksheet which was created by a numerical
simulation. The method below works, but takes a while. I figured that
selecting all the "dead" rows first and then deleting them with one
delete command would be faster (I already have disabled screen updating
and automatic calcs.)
Code:
--------------------
Sub proDeleteRows()
Dim varRecordInterval as Double 'Interval to keep the data
varRecordInterval = 5
Dim varTimeStep As Double 'Timestep used for calculating the data
varRecordInterval = 0.2
Dim varNthKeep As Integer 'Variable to store the "row keeping" interval
varNthKeep = varRecordInterval / varTimeStep ' Calculates N
Dim vanNthCounter As Integer ' Counter used in For Loop
vanNthCounter = 1
'Select the last row of the simulation and temporarily paste its values
'into row 3 so this last row is not lost during the deletion.
Rows(varSimCounter).Select
Selection.Copy
Rows(3).Select
Selection.PasteSpecial Paste:=xlPasteValues
Dim i As Integer 'Counter for the FOR Loop, initialized at 6 (the first non-zero data row)
i = 6
Dim k As Integer 'Counts number of data rows remaining after deletion
k = 0
For i = 6 To varSimCounter / varNthKeep + 10 'Loop from row 6 (first non-zero entry) to where the data should end, plus a buffer to handle any remainder from the division.
If vanNthCounter < varNthKeep Then ' Check to see if the counter is on N
'If not, then:
Rows(i).Select 'Select the i'th row
Selection.Delete Shift:=xlUp 'Delete the row
i = i - 1 'Decrement the loop so it stays on the same row. Note when you delete a row, those below are shifted up.
vanNthCounter = vanNthCounter + 1 'Increment the N counter
ElseIf vanNthCounter = varNthKeep And Cells(k + 1, 1) < "" Then 'If it is on an N row and this is not the last row:
k = k + 1 'Don't delete it, just increment the "number of data rows still left after deletion" counter
vanNthCounter = 1 'Reset the N counter, and allow the loop to go to the next row.
End If
Next
'Paste that original last data row onto the end of the new data.
Rows(3).Select
Selection.Cut
Rows(k + 1).Select
ActiveSheet.Paste
End Sub
--------------------
Any help to make this one work better, or a better "delete all except
Nth Row" technique would be appreciated.
Thanks!
-Alex
--
EphesiansSix
------------------------------------------------------------------------
EphesiansSix's Profile:
http://www.excelforum.com/member.php...o&userid=35721
View this thread:
http://www.excelforum.com/showthread...hreadid=564836