Macro for selecting rows

  • Thread starter Thread starter JOSEPH WEBER
  • Start date Start date
J

JOSEPH WEBER

I have a spreadsheet I am creating from a query ran in another program. I
have gotten pretty far in the macro programming as far as what i would like
to do. There is one part i can't figure out. I have the macro set up so it
looks for a value in sheet two to tell it which lines to copy, but i cant
figure out how to tell Excel to do it. for example, the data in column a is
a name, then all other lines in column a are blank for that particular
person. Once the macro comes across another name, i want it to select the
previous line all the way back up to the name, so i can get that whole
section. I have a counter set up so i can tell it how many lines to copy,
but just can't figure out how to tell Excel to do that.
 
Without seeing your code, can't be too specific, but something similar to
this should work. When you find the first instance of name, make a note of
xStart=ActiveCell.Row

Then, when you finish counting all the rows you want (I'll assume a variable
name of xCount)

Rows(xStart & ":" & xStart+xCount-1).Copy

This should select the rows your wanting.
 
It sounds like you are trying to fill in the blanks.
a


b


c

to become
a
a
a
a
b
b
c
c
c

If so, try

sub fillin()
mc = 1 'col A
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) = "" Then Cells(i - 1, mc) = Cells(i, mc)
Next i
End Sub
 
This works with a little tweaking for my purposes. I ran into another
problem while running this. I have code from a previous macro that want to
use to create a file name based on a cell in the original spreadsheet. spath
= "\\main\MyDocuments\jweber\Supply Invoices\"
ActiveWorkbook.SaveAs spath & ActiveSheet.Range("g1").Value

I have used this before in another spreadsheet, but it work perfect for that
one because i was copying the whole spreadsheet into another workbook. I
need the"g1" to reference the original workbook in order to come up with a
file name. When i use this, i guess it is looking for a value in "g1" of the
new workbook. Is there any way to get it to reference the original workbook
for the file name?
 
Back
Top