Some minor Questions

  • Thread starter Thread starter pierreeng
  • Start date Start date
P

pierreeng

Hi guys, i encounter some problems.

1. Say if i wanna clear the previous record, is ther any better
command than

Worksheets(m).Range("A1:N3000").ClearContents

i assume the largest limit as N3000, but in real case, it is alway
less than it. Hence, any better command that i can directly clear th
sheet content.



2. How can i sense the last row? Say i got 1000+ rows in several sheet
and there might be one to two spacings within the rows with data. I
Programming C, it shld be i = 1: : n and let it loop until condition
reach. How abt in VB? I wanna let it scan from the first row to th
last row.


3. How to copy a row value to anothe row except the first 2 columns?
dun want to copy cells by cells. Can it be done by smthing lik
sheets(2).Rows(n) .value = Sheets(1).Rows(m).value?


Thks. Sorry to ask n trouble so much, but i m very new in VB and i
fact, i have totally no background on it, i juz learnt throug
information on internet
 
I didn't see your other questions.
2. How can i sense the last row? Say i got 1000+ rows in several sheets
and there might be one to two spacings within the rows with data. In
Programming C, it shld be i = 1: : n and let it loop until conditions
reach. How abt in VB? I wanna let it scan from the first row to the
last row.
Click to expand...
If you don't know the number of rows but you know that the last entry in,
say Column A, is the last row, then:
Set Rng=Range("A1",Range("A" & Rows.Count).End(xlUp))
For each i in Rng
'Your code
Next i

3. How to copy a row value to another row except the first 2 columns? I
dun want to copy cells by cells. Can it be done by smthing like
sheets(2).Rows(n) .value = Sheets(1).Rows(m).value?
Click to expand...
Your example doesn't do anything about the 2 columns you mention. To copy
the whole row:
Sheets("SheetName").Rows(n).Copy Sheets("OtherShtName").Rows(m)
Note that there is a space after "Copy"
HTH Otto
 
Otto got #1.

#2. If you can pick out a column that you know always has data, you can do
something like:

dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
end with

If you can't pick out a row, you could use:

dim lastrow as long
with activesheet
lastrow = .cells.specialcells(xlcelltypelastcell).row
end with

be aware that this returns the same row as you'd get if you hit ctrl-End in the
worksheet. It doesn't automatically get reset.


#3. sheets(2).Rows(n).value = Sheets(1).Rows(m).value
sheets(2).cells(n,1).resize(1,2).clearcontents

would wipe out the first two cells in the row.

or
sheets(2).cells(n,3).resize(1,254).value _
= Sheets(1).cells(n,3).resize(1,254).value

would leave the original values in columns A&B alone.
 
Hello, assuming column N is a good column to test for a used range, the following could be good for 1 & 2

Sub One_n_Two(
With Sheets(1
Range(.[a1], .[n65536].End(xlUp)).ClearContent
End Wit
End Su

And I apologize, I might not be following you on #3. I'll offer the following for starters

Sub Three(
Dim MyArr As Varian
Let MyArr = Sheets(1).[transpose(transpose(c6:iv6))
Sheets(2).[a6].Resize(, UBound(MyArr)).Value = MyAr
End Su

Regards
Nate Oliver
 
Back
Top