Macro to border cells, Shift Cells xldown, instead of Shift EntireRow xldown
Posted by Mike Winters on December 01, 2001 7:47 AM
Ok, I have two questions, first:
In a macro that sorts through data on the first sheet, and places the sorted data on the fourth sheet, I use the line:
Rng.EntireRow.Insert Shift:=ShiftxlDown
what is the command that will only shift the cells in that column down? The way it is doing it now, as more data is sorted (and placed to the right of the previously sorted data), it bumps down everything to the left, so by the time my final set of data gets sorted, the first set has a entire page (or two or more) of blank cells. It is imperative I get this figured out, I don't want to have to make a new worksheet for each set of data (that will mean something like 10 new worksheets).
Second question: in the same macro, how can I have it automatically place a xlthin border around all cells within a specified range, which is determined by the last row that has any cells that still have data in it?
IE, if the furthest down any data extends to is, say, row 50, and that is in cell G (but that can change, this needs to search all rows), how do I make the border around all cells in range A1:Z#, where # is the last row with data in it. I know this sounds vague, but I can't think of any way to better explain it. I can send the file to you if you still cannot see what I am trying to do.
Sincerely,
Mike Winters
United States Army
Posted by Juan Pablo G. on December 01, 2001 12:17 PM
Mike, nice to see you again.
Last row in Column G is:
LastRow = Range("G65536").End(xlUp).Row
And to put borders from A1 to Z LastRow use:
Range("A1:Z" & LastRow).BorderAround Weight:=xlThin
Another thing.. (I recognize the Rng variable...) just use:
Rng.Insert xlShiftDown
Hope that helps.
Juan Pablo G.
Posted by Mike Winters on December 01, 2001 1:03 PM
Juan Pablo, Thanks (AGAIN) for the help- I din't want to bug you any more, as you had already helped me so much. The Rng.Insert xlShiftDown line works, but only for the names. The ranks and the dates then either dont display at all, or dont display where they are supposed to. I am still using the code you wrote, with a few minor changes. It now reads:
Rng.Insert xlShiftDown
Set Rng = Rng.Offset(-1)
Rng.Font.Bold = False
Rng = .Cells(i, 6)
Rng.Offset(, 1).HorizontalAlignment = xlCenter
Rng.Offset(, 1) = Format(.Cells(i, 7), "")
Rng.Offset(, 2).HorizontalAlignment = xlCenter
Rng.Offset(, 2) = Format(.Cells(i, 16), "dd-mmm-yy")
I THINK the shifting problem now lies somewhere in the last four lines (rng.offset...), possibly something having to do with i. As far as the bordering thing goes, that is not quite what I am trying for, but it has given me an idea, so I will see if I can figure it out before (once again) asking for your help. Thanks again for all your help up to this point.
Sincerely,
Mike Winters
United States Army
Posted by Mike Winters on December 01, 2001 2:16 PM
Re: Macro to border cells
OK, I lied, I am stumped (again). This may or may not be possible. The way the macro outputs data, it is columns A, B, C have data, D is blank, E, F, G, H, I, J have data, K is blank, L, M, N, O, P, Q have data, R is blank. In the future, the macro will put data into S, T, U, and possibly more columns. Every 3 columns is a set of data (IE. A,B,C is one, D,E,F is the second, H,I,J the third, L,M,N the fourth, O,P,Q the fifth). What I want to do is this:
1. Search first data set (A,B,C,) to find last row with data in it (must only search A,B,C)
2. Place border around each cell (not just the entire section) in that range (IE A2:C#), where # is the last row in that data group with data in one of its cells.
3. Do the same thing for each data group
I tried to write a loop (similar to the one you wrote Juan Pablo, which is the backbone of the macro), but it (of course) did not work. What I tried to do was:
b = 1
For b = .Range("A65536").End(x1Down).Row To 1 Step 1
If .Cells(b, 1) <> "" Then
Next b
Else
LastRow = Range("A65536").End(xlUp).Row
Range("A1:C" & LastRow).BorderAround Weight:=xlThin
Next b
But am still not getting the results I want. I know what I want to do, but I do not know how to implement it. I am hoping somebody out there can enlighten me.
Sincerely,
Mike Winters
United States Army
